VBA alternative to nested IF's?

[A Taylor]

Hi I have a spreadsheet that records payments on various projects, and I have a data analysis sheet that dissects the information into financial year quarters. For each project I have a potential for 50 individual accounts. I have the following nested If statement for Q1 2014 which when copied through the spreadsheet for other quarters turns it into 24MB file

=IF(CY2="2014 Q1",C2)+IF(CZ2="2014 Q1",E2)+IF(DA2="2014 Q1",G2)+IF(DB2="2014 Q1",I2)+IF(DC2="2014 Q1",K2)+IF(DD2="2014 Q1",M2)+IF(DE2="2014 Q1",O2)+IF(DF2="2014 Q1",Q2)+IF(DG2="2014 Q1",S2)+IF(DH2="2014 Q1",U2)+IF(DI2="2014 Q1",W2)+IF(DJ2="2014 Q1",Y2)+IF(DK2="2014 Q1",AA2)+IF(DL2="2014 Q1",AC2)+IF(DM2="2014 Q1",AE2)+IF(DN2="2014 Q1",AG2)+IF(DO2="2014 Q1",AI2)+IF(DP2="2014 Q1",AK2)+IF(DQ2="2014 Q1",AM2)+IF(DR2="2014 Q1",AO2)+IF(DS2="2014 Q1",AQ2)+IF(DT2="2014 Q1",AS2)+IF(DU2="2014 Q1",AU2)+IF(DV2="2014 Q1",AW2)+IF(DW2="2014 Q1",AY2)+IF(DX2="2014 Q1",BA2)+IF(DY2="2014 Q1",BC2)+IF(DZ2="2014 Q1",BE2)+IF(EA2="2014 Q1",BG2)+IF(EB2="2014 Q1",BI2)+IF(EC2="2014 Q1",BK2)+IF(ED2="2014 Q1",BM2)+IF(EE2="2014 Q1",BO2)+IF(EF2="2014 Q1",BQ2)+IF(EG2="2014 Q1",BS2)+IF(EH2="2014 Q1",BU2)+IF(EI2="2014 Q1",BW2)+IF(EJ2="2014 Q1",BY2)+IF(EK2="2014 Q1",CA2)+IF(EL2="2014 Q1",CC2)+IF(EM2="2014 Q1",CE2)+IF(EN2="2014 Q1",CG2)+IF(EO2="2014 Q1",CI2)

Is there a VBA alternative I could try?

Many thanks in anticipation
Stew

 

[ukmikeb]

Stewart

I'll have another look at this (Re referring to columns L to P), but won't be able to reply until tomorrow as I am away from my computer for the rest of today.

 

[A Taylor]

Hi Mike, I have just spent the last hour getting frustrated reading up on arrays etc and getting nowhere, as I copied your formula to the main spreadsheet, and the formula doesn't return any result now.

https://www.dropbox.com/s/ar1io0wr09yknv6/Nested Ifs Revised.xlsx?dl=0

I have cut and pasted into the above - can you see where I have gone wrong?

Sorry for the bother
Thanks
Stew

 

[ukmikeb]

Stewart

I have tried twice to download the file without success.

After 10 minutes I still get a spinning circle and attempting to abort actually freezes my Firefox browser.

I suspect that the file is much too large.

Please, try with a file of 100 records at most.

Thanks.

 

[A Taylor]

https://www.dropbox.com/s/e2v76s9ri3ygae5/Nested Ifs Revised again.xlsx?dl=0

Hi Mike,

I have reduced the information on the spreadsheet and have put in in the Public folder of Dropbox, so hopefully you should be able to read it now.

Thanks again
Stew

 

[ukmikeb]

Stewart

I have shared the updated file on dropbox

https://www.dropbox.com/s/1x9gy9uspuvcvwv/nested ifs revised again.xlsx?dl=0

The amended formula in EW2 :-

=SUMPRODUCT(--(IF(YEAR($D2:$CX2)&" "&LOOKUP(MONTH($D2:$CX2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})=EW$1,1,0))*(MOD(COLUMN($D2:$CX2),2)=0)*--(($C2:$CW2)*MOD(COLUMN($C2:$CW2),2)))

enter with Control-Shift-Enter, drag across and down.

I could supply a variation of the part which creates the year and quarter which would be more efficient, if you like.

hth

 

[A Taylor]

That has fixed it perfectly Mike

I replaced the nested If formulas with your sumproduct array formula and got rid of the cells that calculated the quarter that are now surplus and it has dramatically reduced the size of the file from 24MB to just 2MB - so very pleased

Thanks again for your time and effort - very much appreciated! Have a great weekend
Kind regards
Stew