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

 

[Takae]

This is clumsy to use code...
Need more details.

Sub sample_Taylor()
Dim i As Long, j As Long, buf As Single
j = 3
For i = 103 To 145
If Cells(2, i).Value = "2014 Q1" Then
    buf = Cells(2, j).Value + buf
    j = j + 2
End If
Next
MsgBox buf
End Sub

 

[A Taylor]

Thanks for your reply Takae, Ill try and post an abbreviated spreadsheet to highlight my requirement more clearly

 

[Special-K99]

Since both parts of your IF statement are uniform in relation to each other, ie CY2, CZ2, DA2 etc (increases 1 column at a time) and C2, E2, G2 etc
(increases 2 columns at a time) you should be able to replace the whole of that formula with this:

=INDEX(C2:CI2,MATCH("2014 Q1",CY2:EO2,1)*2+1,1)

This assumes there will only be one occurrence of "2014 Q1" in the range CY2:EO2

 

[A Taylor]

Thanks for your reply Special K99, however there are multiple instances between these cells potentially and i need to sum the values for each quarter. I wish i could post a clip of the spreadsheet, but cant find out how to do that.

I'll try and simplify thouhgh

I have this formula that works out which quarter my payments are in

=IF(C2>0,IF(MONTH(C2)<=12,YEAR(C2),YEAR(C2)+1)&" "&LOOKUP(MONTH(C2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"")

And from return from this data eg 2014 Q4 I have the nested IF that cycles through 50 records to total the payments within each quarter with the previous formula. The trouble is the file size is massive with the nested IF as there are a lot of cells with the formula in it.

Hope the above makes some sense

Thanks again
Stew

 

[Special-K99]

You cant attach files on this forum.
You have to upload the file to an online storage site then place a link to it on this forum.

 

[A Taylor]

https://www.dropbox.com/s/zgw0hbvp8d7sa1v/Nested IFs.xlsx?dl=0

Hopefully the above link will work to an abreviated spreadsheet

 

[ukmikeb]

Hi

Perhaps you are looking for something like this :-
ATaylor

	B	C	D	E	F	G	H	I	J	K
1	Record 1	date	Record 2		Record 3		Record 4		Record 5
2	£1,000.00	01 Jan 14	£950.00	16 Dec 14	£25.00	01 Oct 14	£35.00	09 Aug 14	£35.00	15 Aug 14

<tbody>
</tbody>

Excel 2007


ATaylor

	L	M	N	O	P	Q	R	S	T
1	Record 1	Record 2	Record 3	Record 4	Record 5	2014 Q1	2014 Q2	2014 Q3	2014 Q4
2	2014 Q1	2014 Q4	2014 Q4	2014 Q3	2014 Q3	1000	0	70	975

<tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula L2 =IF(C2>0,IF(MONTH(C2)<=12,YEAR(C2),YEAR(C2)+1)&" "&LOOKUP(MONTH(C2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"") M2 =IF(E2>0,IF(MONTH(E2)<=12,YEAR(E2),YEAR(E2)+1)&" "&LOOKUP(MONTH(E2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"") N2 =IF(G2>0,IF(MONTH(G2)<=12,YEAR(G2),YEAR(G2)+1)&" "&LOOKUP(MONTH(G2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"") O2 =IF(I2>0,IF(MONTH(I2)<=12,YEAR(I2),YEAR(I2)+1)&" "&LOOKUP(MONTH(I2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"") P2 =IF(K2>0,IF(MONTH(K2)<=12,YEAR(K2),YEAR(K2)+1)&" "&LOOKUP(MONTH(K2),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4"}),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula Q2 =SUMPRODUCT(--(IF(YEAR($C2:$K2)&" "&LOOKUP(MONTH($C2:$K2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})=Q$1,1,0))*MOD(COLUMN($C2:$K2),2)*--(($B2:$J2)*MOD(COLUMN($B2:$J2)-1,2))) R2 =SUMPRODUCT(--(IF(YEAR($C2:$K2)&" "&LOOKUP(MONTH($C2:$K2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})=R$1,1,0))*MOD(COLUMN($C2:$K2),2)*--(($B2:$J2)*MOD(COLUMN($B2:$J2)-1,2))) S2 =SUMPRODUCT(--(IF(YEAR($C2:$K2)&" "&LOOKUP(MONTH($C2:$K2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})=S$1,1,0))*MOD(COLUMN($C2:$K2),2)*--(($B2:$J2)*MOD(COLUMN($B2:$J2)-1,2))) T2 =SUMPRODUCT(--(IF(YEAR($C2:$K2)&" "&LOOKUP(MONTH($C2:$K2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})=T$1,1,0))*MOD(COLUMN($C2:$K2),2)*--(($B2:$J2)*MOD(COLUMN($B2:$J2)-1,2))) <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

to reduce the IFs as it is driven by row 1 Headings.

Apologies for it being in two parts due to MrExcelHTMLMaker limitations.

Btw Your quarter calculations can be reduced to :-

=IF(YEAR(C2)>2000,YEAR(C2)&" "&LOOKUP(MONTH(C2),{1,4,7,10},{"Q1","Q2","Q3","Q4"}),"")

hth

 

[A Taylor]

Mike, Thank you so much for taking the time to reply.!

Your formulas work superbly apart from the the array formulas in Q2 to T2, where I get #VALUE! error code. I basically want excel to work out how much money has been spent on each project in a given financial quarter. The Nested Ifs did the job but you look like you have a much better formula in the making

I think the formula maybe needs to refer to cells L2 to P2 to work out which values to add from cells B2 to J2?

Many thanks again Mike
Regards
Stew

 

[A Taylor]

Hi Mike I just realised I had to use ctrl shift and enter - the formula works fine now

many thanks again for your time - much appreciated

Stew