Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"Array" problem?
I would like to compare a ratio of two numbers to ratios of other numbers in
the same columns, without doing the computations all the way along. That is: A B 1 1 2 2 1 3 3 2 5 I would like to compare 2/5 against 1/2 and 1/3, but really want to avoid creating a column (even a hidden one) to do the division each time. I want to create automatically a chart showing the highest ratio, the lowest ratio, and then the latest, most current one. Thanks in advance for any help you can offer. |
#2
|
|||
|
|||
"Array" problem?
On Sun, 16 Oct 2005 15:13:02 -0700, "pdberger"
wrote: I would like to compare a ratio of two numbers to ratios of other numbers in the same columns, without doing the computations all the way along. That is: A B 1 1 2 2 1 3 3 2 5 I would like to compare 2/5 against 1/2 and 1/3, but really want to avoid creating a column (even a hidden one) to do the division each time. I want to create automatically a chart showing the highest ratio, the lowest ratio, and then the latest, most current one. Thanks in advance for any help you can offer. Max = =SUM((MAX((A1:A3)/(B1:B3)))) Min = =SUM((MIN((A1:A3)/(B1:B3)))) Current, assuming that's the last row, i.e. row 3 in this case = INDIRECT("A"&COUNT(A1:A3))/INDIRECT("B"&COUNT(A1:A3)) Then have the chart refer to these three cells. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
"Array" problem?
Ritchard,
Max = =SUM((MAX((A1:A3)/(B1:B3)))) Min = =SUM((MIN((A1:A3)/(B1:B3)))) these formulas return #VALUE! for me unless array entered. You didn't happen to place them in rows 1 & 2 did you? Because then they will appear to return the correct answer but they are simply giving you the value in that row. In either case, array entered or not, the SUM is superfluous. Even if array entered, both formulas will choke on the rage being extended past the actual data and return a #DIV/0! error. I also read the OP differently in that I thought that the OP meant TABLE when he/she said chart, (it seems to me that it would be a strange chart with only three fixed points). That being the case, I assumed that the OP wanted the results set out similar to the original data with the two values not the result of the division. I would therefore suggest for the two values in the max ratio: =INDEX(A1:A1000,MATCH(MAX(IF(1-ISERROR(A1:A1000/B1:B1000),A1:A1000/B1:B1000)),A1:A1000/B1:B1000,0)) and =INDEX(B1:B1000,MATCH(MAX(IF(1-ISERROR(A1:A1000/B1:B1000),A1:A6/B1:B1000)),A1:A1000/B1:B1000,0)) both array entered with Ctrl + Shift + Enter If the OP wanted it as a ratio as in 1:2 then: =INDEX(A1:A1000,MATCH(MAX(IF(1-ISERROR(A1:A1000/B1:B1000),A1:A1000/B1:B1000)),A1:A1000/B1:B1000,0))&":"&INDEX(B1:B6,MATCH(MAX(IF(1-ISERROR(A1:A1000/B1:B1000),A1:A1000/B1:B1000)),A1:A1000/B1:B1000,0)) again array entered For the latest ratio, which like you, I read as being the last in the columns of data, I would suggest the normally entered: =LOOKUP(2,A1:A5/B1:B5,A1:A5) and =LOOKUP(2,A1:A5/B1:B5,B1:B5) or as a ratio: =LOOKUP(2,A1:A5/B1:B5,A1:A5)&":"&LOOKUP(2,A1:A5/B1:B5,B1:B5) -- Regards Sandy Replace@mailinator with @tiscali.co.uk "Richard Buttrey" wrote in message ... On Sun, 16 Oct 2005 15:13:02 -0700, "pdberger" wrote: I would like to compare a ratio of two numbers to ratios of other numbers in the same columns, without doing the computations all the way along. That is: A B 1 1 2 2 1 3 3 2 5 I would like to compare 2/5 against 1/2 and 1/3, but really want to avoid creating a column (even a hidden one) to do the division each time. I want to create automatically a chart showing the highest ratio, the lowest ratio, and then the latest, most current one. Thanks in advance for any help you can offer. Max = =SUM((MAX((A1:A3)/(B1:B3)))) Min = =SUM((MIN((A1:A3)/(B1:B3)))) Current, assuming that's the last row, i.e. row 3 in this case = INDIRECT("A"&COUNT(A1:A3))/INDIRECT("B"&COUNT(A1:A3)) Then have the chart refer to these three cells. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |