IF Statement - Please help!

owgarth

New Member
Joined
Aug 29, 2013
Messages
12
Hello,

I have a long if statement which I would like to shorten, I have an idea on how I want to do this but am not sure if it is possible.

In short: I would like to: IF(A1=B1, C1, [value_if_true]*D1)

My [value_if_true], is a long formula and my [value_if_false] is simply multiflying [value_if_true] by another cell/formula. Is it possible to get excel to return [value_if_true] in the [value_if_false] without just duplicating it?

Thanks :)
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
post your formula, someone maybe able to shorten it
 

owgarth

New Member
Joined
Aug 29, 2013
Messages
12
post your formula, someone maybe able to shorten it


Hi mole999
Here is one of the formulas...

=IF($B$4>=F$6,IFERROR(IF($B$5="Local Currency:",IF($B$2="GLOBAL TOTAL",-SUMIFS(Data!$M:$M,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$O:$O,$B11),IF(LEFT($B$2,3)="XXX",-SUMIF(Data!$A:$A,F$6&"ACTUALS"&$B$2&$B11,Data!$L:$L),-SUMIFS(Data!$L:$L,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$G:$G,LEFT($B$2,4),Data!$O:$O,$B11))),IF($B$2="GLOBAL TOTAL",-SUMIFS(Data!$M:$M,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$O:$O,$B11),IF(LEFT($B$2,3)="XXX",-SUMIF(Data!$A:$A,F$6&"ACTUALS"&$B$2&$B11,Data!$L:$L),-SUMIFS(Data!$L:$L,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$G:$G,LEFT($B$2,4),Data!$O:$O,$B11)))*F$5),0),0)

The green text is the same as the red but *F$5.
If anyone would know a way to shorten this, or if it is possible to return the ture value out of an if satatement, without repeating the formula?

Thanks :)
 

owgarth

New Member
Joined
Aug 29, 2013
Messages
12
I would like the formula to be something like this:

=IF($B$4>=F$6,IFERROR(IF($B$5="Local Currency:",IF($B$2="GLOBAL TOTAL",-SUMIFS(Data!$M:$M,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$O:$O,$B11),IF(LEFT($B$2,3)="XXX",-SUMIF(Data!$A:$A,F$6&"ACTUALS"&$B$2&$B11,Data!$L:$L),-SUMIFS(Data!$L:$L,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$G:$G,LEFT($B$2,4),Data!$O:$O,$B11))),[value_if_true]*F$5),0),0)

But this does not work...
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Edit: this was posted before I noticed the previous post.

In general it is possible to have an IF-statement in a formula like:
Code:
=IF(condition; value-if-true; value-if-false) * formula
So you get either value-if-true * formula or value-if-false * formula.
I guess in your case the value-if-true would be 1 and the value-if-false would be F$5, but I didn't go into the details of your formula.
 

owgarth

New Member
Joined
Aug 29, 2013
Messages
12
Thanks MarcelBeug!

Obviously overthinking this, I have rearranged it, which is shorter. Thanks very much!

=IF($B$4>=F$6,IFERROR(IF($B$2="GLOBAL TOTAL",-SUMIFS(Data!$M:$M,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$O:$O,$B11),IF(LEFT($B$2,3)="XXX",-SUMIF(Data!$A:$A,F$6&"ACTUALS"&$B$2&$B11,Data!$L:$L),-SUMIFS(Data!$L:$L,Data!$E:$E,F$6,Data!$F:$F,"ACTUALS",Data!$G:$G,LEFT($B$2,4),Data!$O:$O,$B11)))*(IF($B$5="Local Currency:",1,F$5)),0),0)

So I guess there is not a way to return the true value as I described.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top