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.

 

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

 

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