CHALLENGE! - USING IF MAX MIN AND LOOKUP TOGETHER

[Guest]

Hi!

USING Excel 2003

I have a table showing depreciation values for three machines.

B29:B31 are labelled Machine a. b and c respectively
c29:c31 have residual values of 9300, 9750 and 9850.
d28:h28 have year numbers 1 to 5
c28 has a depreciation formula which shows value of one particular
depreication
The rest of the table values are filled in using the {Table function.

I have to find out which machine loses the maximum or minimum amount in a
particular year depending on what the user selects. so far, i have a maximum
and minimum drop down and a year dropdown. i also have a formula which is
very long and complicated which works out the value but does not tell me the
machine that produces the value. the formula is:

=IF(C37="Maximum",IF(E37=1,MAX(D2931),IF(E37=2,MAX(E29:E31),IF(E37=3,MAX(F29:F31),IF(E37=4,MAX(G29:G31),IF(E37=5,MAX(H29:H31),""))))),IF(E37=1,MIN(D2931),IF(E37=2,MIN(E29:E31),IF(E37=3,MIN(F29:F31),IF(E37=4,MIN(G29:G31),IF(E37=5,MIN(H29:H31),""))))))

Any ideas on how i can get it to display the machine name and how i can
simplify the formula above?

 

[Max]

One way, for ..

Any ideas on how i can get it to display the machine name

Assuming your formula below which produces the value:

=IF(C37="Maximum",IF(E37=1,MAX ...

is in C53, then you could try in say, D53:

=IF(E37="","",INDEX(B29:B31,MATCH(C53,OFFSET(B28,,MATCH(E37,D28:H28,0)+1,4),
0)-1))

to return the corresponding machine name

(In E37 is the DV to select the year: 1,2, ... 5)

Above assumes that the values within the data table
in D29H31 will not contain any duplicate max or min values

 

[Max]

Last para should read as:

Above assumes that the values within the data table
in D29H31 will not contain any duplicate max or min values
for any one specific year

(with 3rd line added)

 

[JulieD]

Hi

to return the name of the machine matching the min or max (as chosed in K28)
and the year (as chosen in L28) - try

=INDIRECT(ADDRESS(MATCH(IF(K28="Minimum",MIN(OFFSET(C28,1,L28,3)),IF(K28="Maximum",MAX(OFFSET(C28,1,L28,3)),"Choose
min or max")),OFFSET(C28,1,L28,3),0)+28,2))

to return the value use

=IF(K28="Minimum",MIN(OFFSET(C28,1,L28,3)),IF(K28="Maximum",MAX(OFFSET(C28,1,L28,3)),""))

Cheers
JulieD

 

[Max]

... and as a replacement for:

=IF(C37="Maximum",IF(E37=1,MAX ...

(assumed in cell C53)

you could try instead:
=IF(C37="Maximum",MAX(OFFSET($C$28,1,MATCH(E37,$D$28:$H$28,0),3)),IF(C37="Mi
nimum",MIN(OFFSET($C$28,1,MATCH(E37,$D$28:$H$28,0),3)),""))

 

[Guest]

Thanks a lot for all your help max and julied! I really learnt a
lot...gosh...you guys do know your excel really really well.

Sincerely,
Shaheed

 

[Max]

Pleasure` Shaheed !
Thanks for the feedback (from us) ..

 

[hrlngrv]

Max wrote...

.. and as a replacement for: ....
(assumed in cell C53)

you could try instead:
=IF(C37="Maximum",MAX(OFFSET($C$28,1,MATCH(E37,$D$28:$H$28,0),3)),
IF(C37="Minimum",MIN(OFFSET($C$28,1,MATCH(E37,$D$28:$H$28,0),3)),""))

Wouldn't D28:H28 hold 1 to 5 in ascending order? If so, the column to
check is given by the shorter and simpler

INDEX($D$29:$H$31,0,E37)

So another alternative,

=IF(OR(C37={"Maximum","Minimum"}),
LARGE(INDEX($D$29:$H$31,0,E37),IF(C37="Maximum",1,ROWS($D$29:$H$31))),"")

And if this were in C53, then the corrsponding machine could be given
by
=IF(C53<>"",LOOKUP(2,1/(INDEX($D$29:$H$31,0,E37)=C53),$B$29:$B$31),"")

 

[hrlngrv]

JulieD wrote...

to return the name of the machine matching the min or max (as chosed in K28)
and the year (as chosen in L28) - try

=INDIRECT(ADDRESS(MATCH(IF(K28="Minimum",MIN(OFFSET(C28,1,L28,3)),IF(K28="Maximum",
MAX(OFFSET(C28,1,L28,3)),"Choose min or

max")),OFFSET(C28,1,L28,3),0)+28,2))
....

First, what does this wonderful formula return if K28 is neither
"Maximum" or "Minimum"? It's won't be the diagnostic message "Choose
min or max". Untested?

There are always better alternatives to INDIRECT(ADDRESS(..)),
especially when there's as much hard-coding as in your formula above.
See my response to Max.

 

[Max]

Great alternative, Harlan !
Thanks, and for refinements too ..