NESTED

M

Martha

Hi, I set up 6 sets of if statements and give each a "defined name" to
combine them into one, like this:
=sumif(velocis,velocis2,velocis3,velocis4,velocis5,velocis6,0)but it is not
working. What I trying to do is to set up a formula where if a pc is to be up
24 hours and the user enters and outage of 30 minutes, then the if statement
will get back the answer based on a table I lay out. The table is in two
rows. 1st row begins with the 1440 minutes, then 1439.86 and son on until it
gets down to 14.40 minutes. Then the bottom row begins with 100%, then 99.99%
and so on until it gets to 1%. Then I am using the If statement to look up
at the table (the two rows) to get back the answer according to what the end
user enters. Any help would be greatly appreciated. If I can do this in a
simpler way please advice. Thanks Martha

=IF(D23>=O1,O3,IF(D23>=P1,P3,IF(D23>=Q1,Q3,IF(D23>=R1,R3,IF(=IF(D23>=V1,V3,IF(D23>=W1,W3,IF(D23>=X1,X3,IF(D23>=Y1,Y3,IF(D23>=Z1,Z3,IF(D23>=AA1,AA3,0))))))D23>=S1,S3,IF(D23>=T1,T3,IF(D23>=U1,U3,0)))))))=IF(D23>=AB1,AB3,IF(D23>=AC1,AC3,IF(D23>=AD1,AD3,IF(D23>=AE1,AE3,IF(D23>=AF1,AF3,IF(D23>=AG1,AG3,0))))))=IF(D23>=AH1,AH3,IF(D23>=AI1,AI3,IF(D23>=AJ1,AJ3,IF(D23>=AK1,AK3,IF(D23>=AL1,AL3,IF(D23>=AM1,AM3,0))))))=IF(D23>=AN1,AN3,IF(D23>=AO1,AO3,IF(D23>=AP1,AP3,IF(D23>=AQ1,AQ3,IF(D23>=AR1,AR3,IF(D23>=AS1,AS3,0))))))=IF(D23>=AT1,AT3,IF(D23>=AU1,AU3,IF(D23>=AV1,AV3,IF(D23>=AW1,AW3,IF(D23>=AX1,AX3,IF(D23>=AY1,AY3,IF(D23>=AZ1,AZ3,IF(D23>=BA1,BA3,0))))))))
 
M

Max

A simple way is to flip / reverse the reference table in O1:BA3 using Data >
Sort > Options > Sort left to right > OK, Sort by Row1 > Ascending > OK

Then we could use either:

=HLOOKUP(D23,O1:BA3,3)

or

=INDEX(O3:BA3,MATCH(D23,O1:BA1,1))
 

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

Similar Threads


Top