#### owgarth

##### New Member
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

### 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)
post your formula, someone maybe able to shorten it

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

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...

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.

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.

Replies
9
Views
670
Replies
2
Views
180
Replies
3
Views
360
Replies
4
Views
184
Replies
10
Views
309

1,219,036
Messages
6,145,913
Members
450,655
Latest member
Calvest1

### 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.

### Which adblocker are you using?

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

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