Need help doing complicated formula

M

mrl

I need Excel to look in Cell F29 and populate (with one numerical value from
F5:F14) only one of the series of cells H5:H14 dependent on results in F29
falling between ranges set in B5:B14. I know the fomula needs to reside in
H5:H14, but the rest is over my head.

F29 is the result of C27/E27
 
B

Bernard Liengme

You will need to spell that out in more detail - at least for me!
Maybe make up an analogous example.
best wishes
 
M

mrl

Column B represents a number from 95.0% to 99.5%, with each row a different
number. To the right of Column B I need to have Excel fill in the
pre-designated number in the correct row; i.e. >=95.0%<=95.49 should result
in 200% appearing in H5 (since B5 shows 95%) then >=95.5%<=95.99 should
result in 170% appearing in H6 (since B6 shows 95.5%).

The result of a simple formula, which resides in F29 will determine the
percentage rating used above, which is what needs to be either true or false
to fit into the ranges as explained above?

I know I'm inexperienced in writing a complicated formula, and haven't used
Lookup functions at all. I'm guessing that's what needed, not an IF formula?

Please help?
 
M

mrl

Can you show me, please, how to write the IF formula for this example?

The generosity of your knowledge is greatly appreciated.
 
G

Gary

something like...

=IF(AND(A1>=95%,A1<=95.49%),"200%",IF(AND(A1>=95.5%,A1<=95.99%),"170%",""))

I am not sure if i understood the references correctly. You can change the
references if needed and let me know if it works.

Thanks
 
H

Harlan Grove

mrl said:
Column B represents a number from 95.0% to 99.5%, with each row a
different number. To the right of Column B I need to have Excel
fill in the pre-designated number in the correct row; i.e. >=95.0%
<=95.49 should result in 200% appearing in H5 (since B5 shows 95%)
then >=95.5% <=95.99 should result in 170% appearing in H6 (since
B6 shows 95.5%). ....
. . . Lookup functions at all. I'm guessing that's what needed,
not an IF formula?
Click to expand...

Yes, a lookup formula would seem to be best.

=LOOKUP(B3,{0;0.95;0.955;0.96},{"unspecified";2;1.7;"unspecified"})

and format at 0.0%.
 
M

mrl

I will try this formula, and the Lookup formula provided by Harlan - I will
post my results

Thank you very much
 
M

mrl

I will try this one, and the IF formula Gary provided. I will post my
results.

Thank you very much
 
M

mrl

YIPPEE - The IF formula worked like a charm! All 10 rows are
self-populating now, dependent on formula result in my designated cell, which
will change from week to week. THANK YOU THANK YOU
 
G

Gary

I am glad it worked.


mrl said:
YIPPEE - The IF formula worked like a charm! All 10 rows are
self-populating now, dependent on formula result in my designated cell,
which
will change from week to week. THANK YOU THANK YOU
Click to expand...
 
Top