Quick help for Excel formula

Microsoft Microsoft excel 2007 full vers...
August 30, 2010 at 09:23:35
Specs: Windows XP

I hope my question isn't too complex to answer... I will simplify my situation.

If, say I have a formula (30x+40) and say x=5, then it equals 190. However, I want to put a cap on the cell as 180. I want to then have every other possible cell under the same column with a possible formula answer greater than 180 stay as 180. Any other answer less than 180 should remain its true answer.

The problem is, I would like this all under one cell. I can do this with multiple cells (creating another column).

Is it possible?

To make things complicated for those who would like to see what I am really trying to do, here is the formula I have right now:


With this formula, any number below .92 shows up as false...


See More: Quick help for Excel formula

Report •

August 30, 2010 at 10:00:32
How's 'bout:

=IF(1/(1+10^((B$13-B6)/400))>=0.92, ".92", 1/(1+10^((B$13-B6)/400)))

The reason you are getting a FALSE result is that you didn't tell Excel what to return if the logical_test of the IF statement is False.

FALSE is the default answer if a value_if_false is not specified.

P.S. You do realize, don't you, that by placing " " around .92 you are telling Excel to return the Text version of .92?

That may impact what will happen if you refer to that cell in another formula.

Certain Excel functions will see the .92 as a number, but others will see it as Text.

If it's truly a number, don't use the " ".

re: "I hope my question isn't too complex to answer

Not even close! ;-)

Report •

August 30, 2010 at 10:06:51
Oh wow, your formula worked :D Thank you!

I did realize that putting quotes around the .92 would force the cell to come out as .92. The problem I had was the second half of the formula, having the "false" result happen instead of having the true value. I looked on Google for a pretty long time now... Thanks again :)

Report •

August 30, 2010 at 11:22:17

You say you wanted the capped result to be .92 which is why you used ".92"

To retain it as a number and still display .92, remove the double quotes, which results in 0.92 and then format the cell - select 'Number' tab, then 'Custom' format and enter .00 in the 'Type:' box.


Report •

Related Solutions

Ask Question