Excel If Question

[Martynt74]

Quite often i'll do an IF statement saying if X = 0, then 0 otherwise X

Sometimes if X is a hefty formula it can make the it quite messy inside the formula given that it's basically just duplicating the formula for X.

Example

This is my formula for X

=INDEX('OC-BS'!$A$1:$AC$141,MATCH('4-BS'!$B35,'OC-BS'!$A:$A,0),MATCH('4-BS'!I$29,'OC-BS'!$4:$4,0))/INDEX('OC-ISbyMonth'!$A$1:$Y$67,MATCH("12 Months Sales",'OC-ISbyMonth'!$A$1:$A$67,0),MATCH('4-BS'!I29,'OC-ISbyMonth'!$A$3:$Y$3,0))*365

If the above is 0 then i just want it to display nothing. Is there a better way than having my formula as

=IF(INDEX('OC-BS'!$A$1:$AC$141,MATCH('4-BS'!$B35,'OC-BS'!$A:$A,0),MATCH('4-BS'!I$29,'OC-BS'!$4:$4,0))/INDEX('OC-ISbyMonth'!$A$1:$Y$67,MATCH("12 Months Sales",'OC-ISbyMonth'!$A$1:$A$67,0),MATCH('4-BS'!I29,'OC-ISbyMonth'!$A$3:$Y$3,0))*365=0,"",INDEX('OC-BS'!$A$1:$AC$141,MATCH('4-BS'!$B35,'OC-BS'!$A:$A,0),MATCH('4-BS'!I$29,'OC-BS'!$4:$4,0))/INDEX('OC-ISbyMonth'!$A$1:$Y$67,MATCH("12 Months Sales",'OC-ISbyMonth'!$A$1:$A$67,0),MATCH('4-BS'!I29,'OC-ISbyMonth'!$A$3:$Y$3,0))*365)

I know i could do it in formatting but i'd rather do it formula based. Obviously if the above is the only way to do it then so be it.

 

[Confused Stu]

If there is a neater way to do it in one formula, then I've not found it. Potentially you could put in an extra column, put the first formula into the first column (say column B), then put into the second column:

=IF(B2=0,"",B2)

For neatness sake, you can then hide the first column.

Alternatively, you could create a macro where you specify your formula as an additional formula (for example, "MARVTFORMULA") and then your cell would be:

=IF(MARTVFORMULA=0,"",MARTVFORMULA)

But that might well be using a sledgehammer to crack a nut for this.

 

[Martynt74]

Yeah sounds like i'm at the best option without making things lots more work.

Just thought there may have been something as before i learnt about IFERROR i ended up using a convoluted mixture of IF and ISERROR which had similar repetition.

 

[james.miller]

custom cell formatting is the will do it. (right click range >> format cells or [Ctrl+1])

If the result is zero, display nothing; for everything else use the standard 'general' format:

[=0]"";General

you can replace General with whatever format code you want to use when you do want something to be displayed.

edit: oh i just reread the OP, haha. custom cell formatting is the right way if you want to avoid duplication formulas which IMO you absolutely should be doing. It may mean adapting over worksheet formulas but it avoids any potential problems arising from duplication and it also means excel doesnt have to calculate the formula twice every time the answer is above 0. Which i'm guessing would be most of the time.

 

[Martynt74]

Cheers James, it's a good point on Excel calculating a formula twice. Especially as some of my spreadsheets are pretty huge cutting down processing down is becoming more and more important. I hadn't considered that aspect.