Formula for Sequencing Totals
 

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.