Formula for Sequencing Totals

Choose a start cell to put the formula in, say, K3...

In K3 enter & copy down

=SMALL(Range,ROW()-ROW(K$3)+1)

Jeff T wrote:
> I created an Excel sheet for calculating the cost of items when a value is
> entered in two cells. The formula in cost column for each row is:
> =IF(ISERROR(G3/F3),"",G3/F3)
> I also have a cell that shows me the lowest number in the column:
> =MIN(I3:I34)
> I would like to create cells that will show me the next lower number and
> then a cell to show the next lower number to that. I would only like to
> display the 5 lowest numbers and as you can see I can easily show the lowest.
> I don't know what formula to use in the consecutive cells to show the next to
> lowest and so on.
> Thanks for any help.
> Jeff

Thank you for the reply.
In the formula you gave me do I need to replace the Range and Row items with
the actual range of cells in the column where I have the totals? There are no
rows that need to be in the cells with the formula you supplied (I think)
because it's a column that has the final costs in them that I wish to display
in consective order.
Thanks again,
Jeff

"Aladin Akyurek" wrote:

> Choose a start cell to put the formula in, say, K3...
>
> In K3 enter & copy down
>
> =SMALL(Range,ROW()-ROW(K$3)+1)
>
> Jeff T wrote:
> > I created an Excel sheet for calculating the cost of items when a value is
> > entered in two cells. The formula in cost column for each row is:
> > =IF(ISERROR(G3/F3),"",G3/F3)
> > I also have a cell that shows me the lowest number in the column:
> > =MIN(I3:I34)
> > I would like to create cells that will show me the next lower number and
> > then a cell to show the next lower number to that. I would only like to
> > display the 5 lowest numbers and as you can see I can easily show the lowest.
> > I don't know what formula to use in the consecutive cells to show the next to
> > lowest and so on.
> > Thanks for any help.
> > Jeff
>

OR,
From any cell:

=SMALL(Range,ROW(A1))

And copy down.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41F42680.9040308@xs4all.nl...
> Choose a start cell to put the formula in, say, K3...
>
> In K3 enter & copy down
>
> =SMALL(Range,ROW()-ROW(K$3)+1)
>
> Jeff T wrote:
> > I created an Excel sheet for calculating the cost of items when a value
is
> > entered in two cells. The formula in cost column for each row is:
> > =IF(ISERROR(G3/F3),"",G3/F3)
> > I also have a cell that shows me the lowest number in the column:
> > =MIN(I3:I34)
> > I would like to create cells that will show me the next lower number and
> > then a cell to show the next lower number to that. I would only like to
> > display the 5 lowest numbers and as you can see I can easily show the
lowest.
> > I don't know what formula to use in the consecutive cells to show the
next to
> > lowest and so on.
> > Thanks for any help.
> > Jeff

I created an Excel sheet for calculating the cost of items when a value is
entered in two cells. The formula in cost column for each row is:
=IF(ISERROR(G3/F3),"",G3/F3)
I also have a cell that shows me the lowest number in the column:
=MIN(I3:I34)
I would like to create cells that will show me the next lower number and
then a cell to show the next lower number to that. I would only like to
display the 5 lowest numbers and as you can see I can easily show the lowest.
I don't know what formula to use in the consecutive cells to show the next to
lowest and so on.
Thanks for any help.
Jeff

Lets say that you have the totals in F3:F40 and you want to determine 5
smallest/lowest totals in column G from G3 downwards. The formula would
become:

=SMALL($F$3:$F$40,ROW()-ROW(G$3)+1)

copied down for 5 rows.

Jeff T wrote:
> Thank you for the reply.
> In the formula you gave me do I need to replace the Range and Row items with
> the actual range of cells in the column where I have the totals? There are no
> rows that need to be in the cells with the formula you supplied (I think)
> because it's a column that has the final costs in them that I wish to display
> in consective order.
> Thanks again,
> Jeff
>
> "Aladin Akyurek" wrote:
>
>
>>Choose a start cell to put the formula in, say, K3...
>>
>>In K3 enter & copy down
>>
>>=SMALL(Range,ROW()-ROW(K$3)+1)
>>
>>Jeff T wrote:
>>
>>>I created an Excel sheet for calculating the cost of items when a value is
>>>entered in two cells. The formula in cost column for each row is:
>>>=IF(ISERROR(G3/F3),"",G3/F3)
>>>I also have a cell that shows me the lowest number in the column:
>>>=MIN(I3:I34)
>>>I would like to create cells that will show me the next lower number and
>>>then a cell to show the next lower number to that. I would only like to
>>>display the 5 lowest numbers and as you can see I can easily show the lowest.
>>>I don't know what formula to use in the consecutive cells to show the next to
>>>lowest and so on.
>>>Thanks for any help.
>>>Jeff
>>

In my formula and Aladin's, replace "Range" with the actual cells containing
your totals.
A1:A100 - G25:G500 - X5:X5000 - Whatever !

In my formula, that's all that's necessary, before you drag down to copy as
needed.

In Aladin's formula, you'll also have to replace "K3" with the cell address
that you're entering his formula into, and then drag down to copy as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jeff T" <JeffT@discussions.microsoft.com> wrote in message
news:8AEE4807-5048-417A-AAC9-D349644E57B5@microsoft.com...
> Thank you for the reply.
> In the formula you gave me do I need to replace the Range and Row items
with
> the actual range of cells in the column where I have the totals? There are
no
> rows that need to be in the cells with the formula you supplied (I think)
> because it's a column that has the final costs in them that I wish to
display
> in consective order.
> Thanks again,
> Jeff
>
> "Aladin Akyurek" wrote:
>
> > Choose a start cell to put the formula in, say, K3...
> >
> > In K3 enter & copy down
> >
> > =SMALL(Range,ROW()-ROW(K$3)+1)
> >
> > Jeff T wrote:
> > > I created an Excel sheet for calculating the cost of items when a
value is
> > > entered in two cells. The formula in cost column for each row is:
> > > =IF(ISERROR(G3/F3),"",G3/F3)
> > > I also have a cell that shows me the lowest number in the column:
> > > =MIN(I3:I34)
> > > I would like to create cells that will show me the next lower number
and
> > > then a cell to show the next lower number to that. I would only like
to
> > > display the 5 lowest numbers and as you can see I can easily show the
lowest.
> > > I don't know what formula to use in the consecutive cells to show the
next to
> > > lowest and so on.
> > > Thanks for any help.
> > > Jeff
> >

Thanks for the replies. Works great, but when there is no data in the cells
for the totals, like an empty sheet, the cells with formulas you provided
show: #NUM!
When data is entered into the total column cells the results I'm looking for
work fine. How can I supress the #NUM! from displaying?
Thanks again,
Jeff

"Jeff T" wrote:

> I created an Excel sheet for calculating the cost of items when a value is
> entered in two cells. The formula in cost column for each row is:
> =IF(ISERROR(G3/F3),"",G3/F3)
> I also have a cell that shows me the lowest number in the column:
> =MIN(I3:I34)
> I would like to create cells that will show me the next lower number and
> then a cell to show the next lower number to that. I would only like to
> display the 5 lowest numbers and as you can see I can easily show the lowest.
> I don't know what formula to use in the consecutive cells to show the next to
> lowest and so on.
> Thanks for any help.
> Jeff

One way,

=IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jeff T" <JeffT@discussions.microsoft.com> wrote in message
news:AEA5F7BA-3335-4945-B09B-118F6F0CBB03@microsoft.com...
> Thanks for the replies. Works great, but when there is no data in the
cells
> for the totals, like an empty sheet, the cells with formulas you provided
> show: #NUM!
> When data is entered into the total column cells the results I'm looking
for
> work fine. How can I supress the #NUM! from displaying?
> Thanks again,
> Jeff
>
> "Jeff T" wrote:
>
> > I created an Excel sheet for calculating the cost of items when a value
is
> > entered in two cells. The formula in cost column for each row is:
> > =IF(ISERROR(G3/F3),"",G3/F3)
> > I also have a cell that shows me the lowest number in the column:
> > =MIN(I3:I34)
> > I would like to create cells that will show me the next lower number and
> > then a cell to show the next lower number to that. I would only like to
> > display the 5 lowest numbers and as you can see I can easily show the
lowest.
> > I don't know what formula to use in the consecutive cells to show the
next to
> > lowest and so on.
> > Thanks for any help.
> > Jeff

That worked perfectly.
Thanks.
Jeff

"Ragdyer" wrote:

> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Jeff T" <JeffT@discussions.microsoft.com> wrote in message
> news:AEA5F7BA-3335-4945-B09B-118F6F0CBB03@microsoft.com...
> > Thanks for the replies. Works great, but when there is no data in the
> cells
> > for the totals, like an empty sheet, the cells with formulas you provided
> > show: #NUM!
> > When data is entered into the total column cells the results I'm looking
> for
> > work fine. How can I supress the #NUM! from displaying?
> > Thanks again,
> > Jeff
> >
> > "Jeff T" wrote:
> >
> > > I created an Excel sheet for calculating the cost of items when a value
> is
> > > entered in two cells. The formula in cost column for each row is:
> > > =IF(ISERROR(G3/F3),"",G3/F3)
> > > I also have a cell that shows me the lowest number in the column:
> > > =MIN(I3:I34)
> > > I would like to create cells that will show me the next lower number and
> > > then a cell to show the next lower number to that. I would only like to
> > > display the 5 lowest numbers and as you can see I can easily show the
> lowest.
> > > I don't know what formula to use in the consecutive cells to show the
> next to
> > > lowest and so on.
> > > Thanks for any help.
> > > Jeff
>
>

Thanks for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Jeff T" <JeffT@discussions.microsoft.com> wrote in message
news:2180FFB1-285D-4760-823A-44BA4B8E6D86@microsoft.com...
That worked perfectly.
Thanks.
Jeff

"Ragdyer" wrote:

> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Jeff T" <JeffT@discussions.microsoft.com> wrote in message
> news:AEA5F7BA-3335-4945-B09B-118F6F0CBB03@microsoft.com...
> > Thanks for the replies. Works great, but when there is no data in the
> cells
> > for the totals, like an empty sheet, the cells with formulas you
provided
> > show: #NUM!
> > When data is entered into the total column cells the results I'm looking
> for
> > work fine. How can I supress the #NUM! from displaying?
> > Thanks again,
> > Jeff
> >
> > "Jeff T" wrote:
> >
> > > I created an Excel sheet for calculating the cost of items when a
value
> is
> > > entered in two cells. The formula in cost column for each row is:
> > > =IF(ISERROR(G3/F3),"",G3/F3)
> > > I also have a cell that shows me the lowest number in the column:
> > > =MIN(I3:I34)
> > > I would like to create cells that will show me the next lower number
and
> > > then a cell to show the next lower number to that. I would only like
to
> > > display the 5 lowest numbers and as you can see I can easily show the
> lowest.
> > > I don't know what formula to use in the consecutive cells to show the
> next to
> > > lowest and so on.
> > > Thanks for any help.
> > > Jeff
>
>

Why insist on ROW(A1) for it makes a non-robust formula?

Ragdyer wrote:
> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>

Continuing the example...

=IF(ROW()-ROW(G$3)+1<=COUNT($F$3:$F$40),SMALL($F$3:$F$40,ROW()-ROW(G$3)+1),"")

to avoid the #NUM! error with insufficient number of data points.

Jeff T wrote:
> Thanks for the replies. Works great, but when there is no data in the cells
> for the totals, like an empty sheet, the cells with formulas you provided
> show: #NUM!
> When data is entered into the total column cells the results I'm looking for
> work fine. How can I supress the #NUM! from displaying?
> Thanks again,
> Jeff
>
> "Jeff T" wrote:
>
>
>>I created an Excel sheet for calculating the cost of items when a value is
>>entered in two cells. The formula in cost column for each row is:
>>=IF(ISERROR(G3/F3),"",G3/F3)
>>I also have a cell that shows me the lowest number in the column:
>>=MIN(I3:I34)
>>I would like to create cells that will show me the next lower number and
>>then a cell to show the next lower number to that. I would only like to
>>display the 5 lowest numbers and as you can see I can easily show the lowest.
>>I don't know what formula to use in the consecutive cells to show the next to
>>lowest and so on.
>>Thanks for any help.
>>Jeff

Why insist on ROW(A1) for it makes a non-robust formula?

Ragdyer wrote:
> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>

Continuing the example...

=IF(ROW()-ROW(G$3)+1<=COUNT($F$3:$F$40),SMALL($F$3:$F$40,ROW()-ROW(G$3)+1),"")

to avoid the #NUM! error with insufficient number of data points.

Jeff T wrote:
> Thanks for the replies. Works great, but when there is no data in the cells
> for the totals, like an empty sheet, the cells with formulas you provided
> show: #NUM!
> When data is entered into the total column cells the results I'm looking for
> work fine. How can I supress the #NUM! from displaying?
> Thanks again,
> Jeff
>
> "Jeff T" wrote:
>
>
>>I created an Excel sheet for calculating the cost of items when a value is
>>entered in two cells. The formula in cost column for each row is:
>>=IF(ISERROR(G3/F3),"",G3/F3)
>>I also have a cell that shows me the lowest number in the column:
>>=MIN(I3:I34)
>>I would like to create cells that will show me the next lower number and
>>then a cell to show the next lower number to that. I would only like to
>>display the 5 lowest numbers and as you can see I can easily show the lowest.
>>I don't know what formula to use in the consecutive cells to show the next to
>>lowest and so on.
>>Thanks for any help.
>>Jeff

Could you please elaborate on your statement "non robust formula", in
relation to using ROW(A1)?

I'm always willing to learn.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41F4BF2F.1050002@xs4all.nl...
Why insist on ROW(A1) for it makes a non-robust formula?

Ragdyer wrote:
> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>

Could you please elaborate on your statement "non robust formula", in
relation to using ROW(A1)?

I'm always willing to learn.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41F4BF2F.1050002@xs4all.nl...
Why insist on ROW(A1) for it makes a non-robust formula?

Ragdyer wrote:
> One way,
>
> =IF(ISERR(SMALL($I$3:$I$34,ROW(A1))),"",SMALL($I$3:$I$34,ROW(A1)))
>

Inserting one or more rows before the row of the formula cell (just in
order to beautify the sheet, for example) would lead to incorrect
calculations.

RagDyeR wrote:
> Could you please elaborate on your statement "non robust formula", in
> relation to using ROW(A1)?
>
> I'm always willing to learn.

Thanks for the lesson.
I never looked at it in that light before.

So, I guess that means that rows and/or columns should be inserted and/or
deleted before a formula is considered acceptable?<g>
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41F50F3C.1080400@xs4all.nl...

Inserting one or more rows before the row of the formula cell (just in
order to beautify the sheet, for example) would lead to incorrect
calculations.

RagDyeR wrote:
> Could you please elaborate on your statement "non robust formula", in
> relation to using ROW(A1)?
>
> I'm always willing to learn.