IF Statement - Please help!

[owgarth]

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

 

[mole999]

post your formula, someone maybe able to shorten it

 

[owgarth]

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]

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]

Edit: this was posted before I noticed the previous post.

In general it is possible to have an IF-statement in a formula like:

=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]

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.