Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Complicated Formula
Hi. I'm trying to do a very complicated formula.
I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#2
|
|||
|
|||
Hi
Try: =MAX(A1:A10)/MIN(A1:A10) -- Andy. "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#3
|
|||
|
|||
Stephen,
=MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX( A1:A10),A1:A10,FALSE))) HTH, Bernie MS Excel MVP "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#4
|
|||
|
|||
Let me see if I understand what you want.
First, you say you have the data in cells A1-A10 and that data is in columnar form, not a row is this correct? Based on my read of this, I can interpret your question two different ways. 1) You want to divide the Maximum in the series by the Minimum in the series. 2) You want to divide the Maximum in the series, by the minimum of any data listed after your maximum value. Please advise. Regards, Barb Reinhardt "Stephen" wrote: Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#5
|
|||
|
|||
Hi Stephen
cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#6
|
|||
|
|||
Hi. I'm using the first formula you wrote under "cell two". It's working but
its grabbing the number above the column, not the numbers below. Do you know how I could fix this? Example : 1,3,5,2,4, It's taking the 5 then dividing by the 1, not dividing by the 2 (on the other side). Thanks! "JulieD" wrote: Hi Stephen cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#7
|
|||
|
|||
Hi.
I used your below formula and it ends up dividing by the lowest # in the set. Not the lowest # in a row (below the highest) in the set. Do you know how I could fix this? Thanks! "Bernie Deitrick" wrote: Stephen, =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX( A1:A10),A1:A10,FALSE))) HTH, Bernie MS Excel MVP "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#8
|
|||
|
|||
Stephen,
I used my formula, and it worked, at least in the way that I thought you wanted, dividing the max by the lowest number below - in a higher row number, below the mxaimum on the screen, as your example showed. The only thing that I can think of is that we are at cross-terms on our usage of lower, and that you mixed up your example. I can send you a working example, that does it both ways, if you would like. To contact me privately take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Stephen" wrote in message ... Hi. I used your below formula and it ends up dividing by the lowest # in the set. Not the lowest # in a row (below the highest) in the set. Do you know how I could fix this? Thanks! "Bernie Deitrick" wrote: Stephen, =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX( A1:A10),A1:A10,FALSE))) HTH, Bernie MS Excel MVP "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#9
|
|||
|
|||
Hi!
This works provided there will be no empty cells within the range: =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0),,COUNT(A 1:A10)-MATCH(MAX(A1:A10),A1:A10,0))) Biff "Stephen" wrote in message ... Hi. I'm using the first formula you wrote under "cell two". It's working but its grabbing the number above the column, not the numbers below. Do you know how I could fix this? Example : 1,3,5,2,4, It's taking the 5 then dividing by the 1, not dividing by the 2 (on the other side). Thanks! "JulieD" wrote: Hi Stephen cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#10
|
|||
|
|||
Here's another way...
B1: =MAX(A1:A10) C1: =B1/MIN(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10) This will allow empty cells within the range. Note, the formula will return a #DIV/0! error if zero is the lowest number in the relevant range or no numbers exist within that range. The formula can be modified to deal with these situations, if needed. Hope this helps! In article , "Biff" wrote: Hi! This works provided there will be no empty cells within the range: =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0),,COUNT(A 1:A10)-MATCH(MA X(A1:A10),A1:A10,0))) Biff "Stephen" wrote in message ... Hi. I'm using the first formula you wrote under "cell two". It's working but its grabbing the number above the column, not the numbers below. Do you know how I could fix this? Example : 1,3,5,2,4, It's taking the 5 then dividing by the 1, not dividing by the 2 (on the other side). Thanks! "JulieD" wrote: Hi Stephen cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#11
|
|||
|
|||
Hi!
You know, I was thinking after I posted that none of the replies so far (including mine) has accounted for the fact that the max value may be the last value in the range and would cause a return of #DIV/0!. And then there's the possibility of dupe max's ......Which MIN do you want after which dupe MAX ..... Biff "Domenic" wrote in message ... Here's another way... B1: =MAX(A1:A10) C1: =B1/MIN(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10) This will allow empty cells within the range. Note, the formula will return a #DIV/0! error if zero is the lowest number in the relevant range or no numbers exist within that range. The formula can be modified to deal with these situations, if needed. Hope this helps! In article , "Biff" wrote: Hi! This works provided there will be no empty cells within the range: =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0),,COUNT(A 1:A10)-MATCH(MA X(A1:A10),A1:A10,0))) Biff "Stephen" wrote in message ... Hi. I'm using the first formula you wrote under "cell two". It's working but its grabbing the number above the column, not the numbers below. Do you know how I could fix this? Example : 1,3,5,2,4, It's taking the 5 then dividing by the 1, not dividing by the 2 (on the other side). Thanks! "JulieD" wrote: Hi Stephen cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#12
|
|||
|
|||
=B1/MIN(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10)
I like that one! Biff "Domenic" wrote in message ... Here's another way... B1: =MAX(A1:A10) C1: =B1/MIN(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10) This will allow empty cells within the range. Note, the formula will return a #DIV/0! error if zero is the lowest number in the relevant range or no numbers exist within that range. The formula can be modified to deal with these situations, if needed. Hope this helps! In article , "Biff" wrote: Hi! This works provided there will be no empty cells within the range: =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0),,COUNT(A 1:A10)-MATCH(MA X(A1:A10),A1:A10,0))) Biff "Stephen" wrote in message ... Hi. I'm using the first formula you wrote under "cell two". It's working but its grabbing the number above the column, not the numbers below. Do you know how I could fix this? Example : 1,3,5,2,4, It's taking the 5 then dividing by the 1, not dividing by the 2 (on the other side). Thanks! "JulieD" wrote: Hi Stephen cell 1 =MAX(A1:A10) cell 2 =MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)) ) or =A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0))) where A12 holds the formula of "cell 1" above -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Stephen" wrote in message ... Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before the 9 and find the lowest number in a row and divide cell one's number by this lower number. So it would find 1 is the lowest number in a row and divide 9/1, bringing you to the answer of 9. Thanks for you help! -- Thanks! Stephen |
#13
|
|||
|
|||
In article ,
"Biff" wrote: You know, I was thinking after I posted that none of the replies so far (including mine) has accounted for the fact that the max value may be the last value in the range and would cause a return of #DIV/0!. It seems that in all cases, including mine, an error would be returned. But I think that may be okay, since it would alert the user of that fact. And then there's the possibility of dupe max's ......Which MIN do you want after which dupe MAX ..... Good question. I assumed, like everyone else, the minimum after the first duplicate. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Complicated Pie Chart formula | Charts and Charting in Excel | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |