Returning a zero value in a simple excel formulae

[Smudger1234]

Hi all,

I'm new to this site....and hoping you might be able to help me solve the below. I'm trying to set up a simple quote sheet for my boss as follows:

=SUM(B6*C6)+(D6*$E$3/1000)

Cell E6 = the quantity
Cell B6 = This is a manual import and can be either zero, or 1, 2, 3, 4 etch
Cell C6 = This is a static set up cost
Cell D6 = This is a cost per thousand

So for example:
(1*£250)+(£1*100,000/1,000) returns a correct cost of £350.00 (in cell E6)

The problem I'm trying to solve is that when I put in a zero in cell B6 - I want the total in E6 to also return a value of £0.00....however it still picks up static set up cost of £250.00

Any ideas please?

thanks very much

[Sam Capricci]

Add =iferror((your formula),0)

[Smudger1234]

Hi, thanks for that....but sorry, this is now the formulae, but it's now returning £100.00 (so picking up the £1*100,000/1,000)

=IFERROR(((B6*C6)+(D6*$E$3/1000)),0)

(1*£250)+(£1*100,000/1,000)

[Sam Capricci]

Sorry I misunderstood your issue ( typing from iPad so give me a couple seconds to work out the details)

[Sam Capricci]

So would this work for you? =if(b6<>0,(your formula),0)

[Glenn Kennedy]

or... same end result, but other way round!!

=IF(B6=0,0,SUM(B6*C6)+(D6*$E$3/1000))

[samba_ravi]

=IF(B6=0,0,B6*C6+D6*$E$3/1000)
I think no need of sum here

[Sam Capricci]

or... same end result, but other way round!!

=IF(B6=0,0,SUM(B6*C6)+(D6*$E$3/1000))

Ah, choices, it's good to have many!

[Smudger1234]

Hi all, thanks so much for the quick response - got the problem solved now!

Thank you.