Forum Discussion

Copper Contributor

Apr 05, 2018

Solved

How to use IF formula to change cell value if below “value” but keeping additional formula

Hi forum, I am working on a test document to learn excel, below is my problem. What I am wanting to do is make Cell D6 show “0” if Cell C6 is below 11500 but if Cell C6 isn’t below 11500 then Ce...

Excel Online

Formulas & Functions

Haytham Amairah
Apr 05, 2018
Hi Lee,

My formula in the previous reply is this:

=IF(C6<11500,0,SUM(C6-F14)*D14)

But your formula in the cell D6 is this:

=IF(C6<11500,"0","SUM(C6-F14)*D14")

Please compare them and note the difference!

I don't know why you put the double quotes around the zero and the SUM formula!

Double quotation marks disable any calculation, and converts it to text!

Lee Furber

Copper Contributor

Apr 05, 2018

As you can see when C6 value dropped below 11500 it gives us the "0" in D6 in which I wanted, however, as you can see in the second picture when C6 value goes above 11500 it displays the formula in D6 rather than the actual calculation I'm after.

I apologise for the complexity but feel like you'll be able to get me there in the end :)

Haytham Amairah

Silver Contributor

Apr 05, 2018

Hi Lee,

My formula in the previous reply is this:

=IF(C6<11500,0,SUM(C6-F14)*D14)

But your formula in the cell D6 is this:

=IF(C6<11500,"0","SUM(C6-F14)*D14")

Please compare them and note the difference!

I don't know why you put the double quotes around the zero and the SUM formula!

Double quotation marks disable any calculation, and converts it to text!

Lee Furber
Copper Contributor
Apr 05, 2018
You sir are amazing, you’re right it didn’t need the “ although I only added that as I thought “ defines the statement you’re making