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(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?
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?