Excel Fomula help

secret_tramp

Hi, I am trying to get Excel to perform a calculation only if one of the cells has a number greater than 0 in it. Is this possible please? I have seen tutorials mentioning IF and WHATIF but can't get my head round it.


Many thanks in advance for any words of wisdom, and better still how to express this in the equation.

Agrajag

Use =IF(condition, value if true, value if false)

so if you want to calculate 100 times the value in A1, but only if it is >0, then enter (in another cell)
=IF(A1>0,100*A1,"Value is <=0")
This will show 100 * A1 (if A1>0),
but will show "Value is <=0" if A1 IS <=0
It is pretty versatile.
You can use AND(condition,condition) or OR(condition,condition) if you want to check more than one thing.For example, to get the sum of A1 and B1, but only if they are both >0, use
=IF(AND(A1>0,B1>0),A1+B1,-1)
This time it will show -1 if either A1 or B1 is <=0

Come back if this isn't clear

grumbler

The calculation will be performed regardless of the value.
The result can depend on the value.

E.g. =IF(A1>0,B1*C1, "")

Boxman

Using IF(B4>0,B4*10,0) in the destination cell will give the result of B4 times 10 when B4 is greater than 0 or 0 otherwise. If you want anything else in the destination cell when B4 does equal 0 then replace the last 0 in the above formula with whatever you want - if this is to be text then enclose it in quotes. For example if you want the word Martin in there when B4=0 then it would read IF(B4>0,B4*10,"Martin") or if you want the cell blank just use ""


Hope this helps.

secret_tramp

Thanks for your answers. Clearly I am well out of my depth so I will explain what I am trying to do.


The current calculation is as follows:


=(D4*D5)+12.95+(D4*D5*0.005)


Essentially it is to work out the total cost of buying some shares to take into account dealing costs and 0.5% stamp duty.


Currently if there is no value in D4 then we end up with an answer of 12.95 which I don't want.


How do we change the equation to perform a calculation only if there is a value in D4?

Mirno

=IF(ISBLANK(D4), "", (D4*D5*1.005)+12.95)

Note that I changed your formula slightly - multiplying by 1.005 avoids the need to add D4*D5 again.

GunJack

boxman's post above, your formula needs putting in instead of B4*10 in his example, so:-

IF(D4>0,((D4*D5)+12.95+(D4*D5*0.005)),0)

should do it

GunJack

Mirno wrote: »

=IF(ISBLANK(D4), "", (D4*D5*1.005)+12.95)

Note that I changed your formula slightly - multiplying by 1.005 avoids the need to add D4*D5 again.

might be better to have 0 rather than blanks, makes filtering etc a bit easier, esp if the OP is a relative novice...just a thought...

secret_tramp

Many thanks. Copying and pasting Mirno's formula did the trick. Very impressive knowledge round here.