Formula for Sequencing Totals

[Jeff T]

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

 

[Aladin Akyurek]

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]

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

 

[Ragdyer]

OR,
From any cell:

=SMALL(Range,ROW(A1))

And copy down.

 

[Aladin Akyurek]

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.

 

[Ragdyer]

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.

 

[Jeff T]

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

 

[Ragdyer]

One way,

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

 

[Jeff T]

That worked perfectly.
Thanks.
Jeff

One way,

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

 

[RagDyeR]

Thanks for the feed-back.

 

[Aladin Akyurek]

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

 

[Aladin Akyurek]

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.

 

[RagDyeR]

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

I'm always willing to learn.

 

[Aladin Akyurek]

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]

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!
-------------------------------------------------------------------


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.