Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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))) |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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))) |
#14
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |