Hello,
I’m using Excel 2010 ( no comments!) and I have an extensive monthly report based on dynamic table and nested IFs but I wonder if there is a better and simpler solution.
I have read some questions in the forum and it seems difficult to achieve the same results with a more elegant formula. I haven’t found an alternative in the previous answers suitable to my case. Maybe I’m missing out?
Each of my dynamic tables includes the monthly data of 4 years (13 columns per year: 12 months+total)
In order to compare the evolution in similar periods, I must show the accumulated total up to certain month of every year.
To do so I use additional columns out of the table. According to my nested IFs formula, if I choose the cell containing the text “February” , the destination cell sums columns from January to February, if I choose “November”, the cell sums columns from January to November….
The result is a very long formula:
=IF(CH$7=Q$7,SUM($Q8:$Q8),IF(CH$7=R$7,SUM($Q8:$R8),IF(CH$7=S$7,SUM($Q8:$S8),IF(CH$7=T$7,SUM($Q8:$T8),IF(CH$7=U$7,SUM($Q8:$U8),IF(CH$7=V$7,SUM($Q8:$V8),IF(CH$7=W$7,SUM($Q8:$W8),IF(CH$7=X$7,SUM($Q8:$X8),IF(CH$7=Y$7,SUM($Q8:$Y8),IF(CH$7=Z$7,SUM($Q8:$Z8),IF(CH$7=AA$7,SUM($Q8:$AA8),IF(CH$7=AB$7,SUM($Q8:$AB8)))))))))))))
Can I use a simple strategy in my old Excel 2010?
Thanks in advance
I’m using Excel 2010 ( no comments!) and I have an extensive monthly report based on dynamic table and nested IFs but I wonder if there is a better and simpler solution.
I have read some questions in the forum and it seems difficult to achieve the same results with a more elegant formula. I haven’t found an alternative in the previous answers suitable to my case. Maybe I’m missing out?
Each of my dynamic tables includes the monthly data of 4 years (13 columns per year: 12 months+total)
In order to compare the evolution in similar periods, I must show the accumulated total up to certain month of every year.
To do so I use additional columns out of the table. According to my nested IFs formula, if I choose the cell containing the text “February” , the destination cell sums columns from January to February, if I choose “November”, the cell sums columns from January to November….
The result is a very long formula:
=IF(CH$7=Q$7,SUM($Q8:$Q8),IF(CH$7=R$7,SUM($Q8:$R8),IF(CH$7=S$7,SUM($Q8:$S8),IF(CH$7=T$7,SUM($Q8:$T8),IF(CH$7=U$7,SUM($Q8:$U8),IF(CH$7=V$7,SUM($Q8:$V8),IF(CH$7=W$7,SUM($Q8:$W8),IF(CH$7=X$7,SUM($Q8:$X8),IF(CH$7=Y$7,SUM($Q8:$Y8),IF(CH$7=Z$7,SUM($Q8:$Z8),IF(CH$7=AA$7,SUM($Q8:$AA8),IF(CH$7=AB$7,SUM($Q8:$AB8)))))))))))))
Can I use a simple strategy in my old Excel 2010?
Thanks in advance
MR EXCEL_NESTED IF.xlsx | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | P | AC | AP | BC | BP | CC | CD | CE | CF | CG | CH | CI | CJ | CK | CL | CO | CP | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1 | 01-January | 02-February | 03-March | 04-April | 05-May | 06-June | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2 | 07-July | 08-August | 09-Setember | 10-October | 11-November | 12-December | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3 | Copy-paste from above: | 11-November | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5 | . | DIFF | DIFF | DIFF | DIFF | Up to month | Up to month | Up to month | Up to month | Up to month | DIFF | DIFF | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
6 | Total 2015 | Total 2016 | Total 2017 | Total 2018 | Total 2019 | Total 2020 | FULL YEAR | FULL YEAR | FULL YEAR | FULL YEAR | 2.016 | 2.017 | 2.018 | 2.019 | 2.020 | CURRENT MONTH | CURRENT MONTH | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7 | 2017-2016 | 2018-2017 | 2019-2018 | 2020-2019 | 11-November | 11-November | 11-November | 11-November | 11-November | 2019-2018 | 2020-2019 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
8 | Type A | 110.421,31 | 169.471,57 | 169.975,92 | 65.837,81 | 38.822,20 | 2,00 | 504,35 | -104.138,11 | -27.015,61 | -38.820,20 | 149.995,62 | 162.916,85 | 62.536,61 | 37878,8 | 2,00 | -24.657,81 | -37.876,80 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
CH7:CL7 | CH7 | =+$CK$3 |
CC8 | CC8 | =SUM(BQ8:CB8) |
CD8 | CD8 | =AP8-AC8 |
CE8 | CE8 | =+BC8-AP8 |
CF8 | CF8 | =+BP8-BC8 |
CG8 | CG8 | =+CC8-BP8 |
CH8 | CH8 | =IF(CH$7=Q$7,SUM($Q8:$Q8),IF(CH$7=R$7,SUM($Q8:$R8),IF(CH$7=S$7,SUM($Q8:$S8),IF(CH$7=T$7,SUM($Q8:$T8),IF(CH$7=U$7,SUM($Q8:$U8),IF(CH$7=V$7,SUM($Q8:$V8),IF(CH$7=W$7,SUM($Q8:$W8),IF(CH$7=X$7,SUM($Q8:$X8),IF(CH$7=Y$7,SUM($Q8:$Y8),IF(CH$7=Z$7,SUM($Q8:$Z8),IF(CH$7=AA$7,SUM($Q8:$AA8),IF(CH$7=AB$7,SUM($Q8:$AB8))))))))))))) |
CI8 | CI8 | =IF(CI$7=AD$7,SUM($AD8:$AD8),IF(CI$7=AE$7,SUM($AD8:$AE8),IF(CI$7=AF$7,SUM($AD8:$AF8),IF(CI$7=AG$7,SUM($AD8:$AG8),IF(CI$7=AH$7,SUM($AD8:$AH8),IF(CI$7=AI$7,SUM($AD8:$AI8),IF(CI$7=AJ$7,SUM($AD8:$AJ8),IF(CI$7=AK$7,SUM($AD8:$AK8),IF(CI$7=AL$7,SUM($AD8:$AL8),IF(CI$7=AM$7,SUM($AD8:$AM8),IF(CI$7=AN$7,SUM($AD8:$AN8),IF(CI$7=AO$7,SUM($AD8:$AO8))))))))))))) |
CJ8 | CJ8 | =IF(CJ$7=AQ$7,SUM($AQ8:$AQ8),IF(CJ$7=AR$7,SUM($AQ8:$AR8),IF(CJ$7=AS$7,SUM($AQ8:$AS8),IF(CJ$7=AT$7,SUM($AQ8:$AT8),IF(CJ$7=AU$7,SUM($AQ8:$AU8),IF(CJ$7=AV$7,SUM($AQ8:$AV8),IF(CJ$7=AW$7,SUM($AQ8:$AW8),IF(CJ$7=AX$7,SUM($AQ8:$AX8),IF(CJ$7=AY$7,SUM($AQ8:$AY8),IF(CJ$7=AZ$7,SUM($AQ8:$AZ8),IF(CJ$7=BA$7,SUM($AQ8:$BA8),IF(CJ$7=BB$7,SUM($AQ8:$BB8))))))))))))) |
CK8 | CK8 | =IF(CK$7=BD$7,SUM($BD8:$BD8),IF(CK$7=BE$7,SUM($BD8:$BE8),IF(CK$7=BF$7,SUM($BD8:$BF8),IF(CK$7=BG$7,SUM($BD8:$BG8),IF(CK$7=BH$7,SUM($BD8:$BH8),IF(CK$7=BI$7,SUM($BD8:$BI8),IF(CK$7=BJ$7,SUM($BD8:$BJ8),IF(CK$7=BK$7,SUM($BD8:$BK8),IF(CK$7=BL$7,SUM($BD8:$BL8),IF(CK$7=BM$7,SUM($BD8:$BM8),IF(CK$7=BN$7,SUM($BD8:$BN8),IF(CK$7=BO$7,SUM($BD8:$BO8))))))))))))) |
CL8 | CL8 | =IF(CL$7=BE$7,SUM($BQ8:$BQ8),IF(CL$7=BF$7,SUM($BQ8:$BR8),IF(CL$7=BG$7,SUM($BQ8:$BS8),IF(CL$7=BH$7,SUM($BQ8:$BT8),IF(CL$7=BI$7,SUM($BQ8:$BU8),IF(CL$7=BJ$7,SUM($BQ8:$BV8),IF(CL$7=BK$7,SUM($BQ8:$BW8),IF(CL$7=BL$7,SUM($BQ8:$BX8),IF(CL$7=BM$7,SUM($BQ8:$BY8),IF(CL$7=BN$7,SUM($BQ8:$BZ8),IF(CL$7=BO$7,SUM($BQ8:$CA8),IF(CL$7=BP$7,SUM($BQ8:$CB8))))))))))))) |
CO8:CP8 | CO8 | =+CK8-CJ8 |