"Array" problem?

[Guest]

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.

 

[Richard Buttrey]

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
__________________________

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk