CHALLENGE! - USING IF MAX MIN AND LOOKUP TOGETHER

S

SHAHEED

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(D29:D31),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(D29:D31),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?
 
M

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
 
M

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)
 
J

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
 
M

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)),""))
 
S

SHAHEED

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
 
H

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),"")
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top