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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### mole999

##### Well-known Member
post your formula, someone maybe able to shorten it

#### owgarth

##### New Member
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
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
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
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. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,533
Messages
5,854,291
Members
431,636
Latest member
shabbas313 ### 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