if functions returning different cell values to a question

[Guest]

I am trying to write a formula that says if the value of cell D78 is =<79 then return the value in cell E71 but if the value in cell D78 is greater than 79 but less the 100 return the value in cell E72, but if the value in cell D78 is equal to or greater than 100 but less than 120 return the value in E73, or if the value in D78 is equal or greater than 120 but less than 140 return the value in E74 and finally if the value in D78 is greater than 140 return than the value in E74. Help I am stumped

 

[Bob Phillips]

=IF(D78<=79,E71,IF(D78<100,E72,IF(D78<120,E73,IF(D78<140,E74,E74))))

You have the same cell for <140 and > 140 as E74, so the last E74 might be
140. Also you don't say what happens if = 140, I have assumed it goes with
the E74 that might really mean E75.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am trying to write a formula that says if the value of cell D78 is =<79

then return the value in cell E71 but if the value in cell D78 is greater
than 79 but less the 100 return the value in cell E72, but if the value in
cell D78 is equal to or greater than 100 but less than 120 return the value
in E73, or if the value in D78 is equal or greater than 120 but less than
140 return the value in E74 and finally if the value in D78 is greater than
140 return than the value in E74. Help I am stumped

 

[Niek Otten]

Check your specifications again for what should happen if the D78 is 130 or
145.

But if it is as you specified:

=IF(D78<=79,E71,IF(D78<100,E72,IF(D78<120,E73,E74)))

You should also check HELP for the VLOOKUP() function, in case you have more
threshold values then the present three.
--
Kind Regards,

Niek Otten

Microsoft MVP - Excel

I am trying to write a formula that says if the value of cell D78 is =<79

then return the value in cell E71 but if the value in cell D78 is greater
than 79 but less the 100 return the value in cell E72, but if the value in
cell D78 is equal to or greater than 100 but less than 120 return the value
in E73, or if the value in D78 is equal or greater than 120 but less than
140 return the value in E74 and finally if the value in D78 is greater than
140 return than the value in E74. Help I am stumped

 

[Guest]

Thanks
I couldn't get anything but values in earlier attempts. I did actually modify the formula to include above 140 and ended up with this
=IF(D78<=79,E71,IF(D78<100,E72,IF(D78<120,E73,IF(D78<140,E74,IF(D78>140,E75,E75)))))

 

[Bob Phillips]

You don't need the last if as it does the same whatever condition, so this
will suffice

=IF(D78<=79,E71,IF(D78<100,E72,IF(D78<120,E73,IF(D78<140,E74,E75))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks,
I couldn't get anything but values in earlier attempts. I did actually

modify the formula to include above 140 and ended up with this.=IF(D78<=79,E71,IF(D78<100,E72,IF(D78<120,E73,IF(D78<140,E74,IF(D78>140,E75,
E75)))))