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 :)
 

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
 
Upvote 0
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 :)
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top