Excel Formula Help

Kido250

I'm trying to compare some values in Excel. What I'd like is a formula that will take the result (W/L) in column G and then if W will put the sum of D & F columns in column H and if L will put 0. At the moment I've got the first half of the formula =IF(G2="W",D2*F2), but column H is showing as False and ideally I'd like it to show 0. Does anyone know ow to do this? I've tried Googling but I never get quite what I want. Thanks.

NaughtiusMaximus

You just need to add an element to your formula, try:

=IF(G2="W",D2*F2,0)

Kido250

Perfect. Knew it it would be easy once I knew the answer.

Heedtheadvice

Perfect? Far from it!!

That formula might give you the answer you need... (If indeed the maths was correct!!!)

But it does not give you the answer you asked for!

"if W will put the sum of D & F columns in column H and if L will put 0"

It will do the multiplication answer but will give you 0 for all other values of col G. You asked for zero when G is L! For that you need to add another if statement to give 0 for L and a default value (like the 0 in that first if statement) for all other values.

That might not matter of course if you just happened to phrase your question incorrectly?
The formula ought to be:

=IF(G2="W",D2+F2,0) (not a multiplication but an addition)

or

=IF(G2="W",D2+F2,IF(G2="L",0, a default value or text))

Consider what result you would want, for example if cell in col G was X or held no value (or for that matter an error value or #n/a if it is a calculated cell)

Kido250

Thanks for the help. I now have 5 formals which work independently but I need to put them together so the answer comes out depending on whether a, b, c, d or e is in cell G17. The 5 formulas I have are:
=IF(G17="a",(D17/2)*D17/5+D17/2)
=IF(G17="b",(D17/2)*F17+(D17/2)*D17/5+D17/2)
=IF(G17="c",(D19))
=IF(G17="d",0)
=IF(G17="e",(D17*F17))
Is this possible?

WaywardDriver

Could use nested IF formulae but messy. Simplest is:
=IF(G17="a",(D17/2)*D17/5+D17/2,0)+IF(G17="b",(D17/2)*F17+(D17/2)*D17/5+D17/2,0)+IF(G17="c",(D19),0)+IF(G17="d",0)+IF(G17="e",(D17*F17),0)
with no spaces anywhere.

Heedtheadvice

Are you sure this is what you mean..=IF(G17="a",(D17/2)*D17/5+D17/2)....

Do check your maths and order of calculation!

Far better to do individual formulas in columns next to each other and then just select the column based upon col G's contents for the final answer....just one other way of doing- it because as wayward says long nested formulae are not the simplest to view and are therefore prone to mistakes. Not as bad with named ranges but that's another matter.

moneysavinghero

Newer versions of Excel have the SWITCH function which makes things a little less messy than using lots of nested IF's
=SWITCH(G17,"A",value,"B",value,"C",value,"D",value",E",value, default)
Replace value with values (or formulas) you want and replace default with a default value

WaywardDriver

moneysavinghero said:

Newer versions of Excel have the SWITCH function which makes things a little less messy than using lots of nested IF's
=SWITCH(G17,"A",value,"B",value,"C",value,"D",value",E",value, default)
Replace value with values (or formulas) you want and replace default with a default value

You learn something new every day! Available from Office 2019. Not case-dependent so "a" same as "A".