ateeqsindhu
New Member
- Joined
- Jan 31, 2021
- Messages
- 10
- Office Version
- 365
- 2019
- 2016
- 2013
- 2010
- Platform
- Windows
i have a data set wherein few fields contain data but some also hold NUL due to which my calculation is changed. this is not complex since it is not standard as any column out of 4 columns can be NUL hence making it difficult to fix the formula.
Data for all 12 months | Data for 6 months | MY Result with Flaws | Requirement | |||||||||||||
=COUNTA(C5:F5) | =COUNTA(C5) | =COUNTA(D5) | =COUNTA(E5) | =COUNTA(F5) | =IF(COUNTA(C4,E4)=2, (B4/((C4+E4)/18)), (B4/((C4+E4)/12))) | =IF(COUNTA(D4,F4)=2, (A4/((D4+F4)/18)), (A4/((D4+F4)/12))) | calculate %age based upon either 18 months or 12 or even 6 months where appliable | |||||||||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | ||
CR_TURNOVER | DR_TURNOVER | ACTUAL_DR_TURNOVER_21 | ACTUAL_CR_TURNOVER_21 | ACTUAL_DR_TURNOVER_22 | ACTUAL_CR_TURNOVER_22 | Count A | Count A | Count A | Count A | Count A | DR Tun Vs Act Dr | CR Tun Vs Act CR | DR Tun Vs Act Dr | CR Tun Vs Act CR | ||
600,000 | 600,000 | 345 | 81,501 | 1,238,240 | 1,976,600 | 4 | 1 | 1 | 1 | 1 | 872% | 525% | ||||
600,000 | 700,000 | 6,994,701 | 7,001,985 | 5,526,495 | 5,620,000 | 4 | 1 | 1 | 1 | 1 | 101% | 86% | ||||
900,000 | 700,000 | 300,173 | 952,000 | 2 | 0 | 0 | 1 | 1 | 2798% | 1134% | ||||||
1,000,000 | 900,000 | 4,832,154 | 3,495,000 | 5,024,149 | 5,400,000 | 4 | 1 | 1 | 1 | 1 | 164% | 202% | ||||
300,000 | 350,000 | 13,669,782 | 11,579,578 | 160,487 | 3,246 | 4 | 1 | 1 | 1 | 1 | 46% | 47% | ||||
4,834,200 | 3,799,000 | 45,587,752 | 58,009,782 | 40,620,393 | 24,600,974 | 4 | 1 | 1 | 1 | 1 | 79% | 105% | ||||
800,000 | 700,000 | 547,000 | 800,173 | 281,308 | 3 | 0 | 1 | 1 | 1 | 1050% | 1738% | |||||
800,000 | 650,000 | 547,000 | 800,173 | 281,308 | 3 | 0 | 1 | 1 | 1 | 975% | 1738% | |||||
200,000 | 150,000 | 100,000 | 197,279 | 2 | 1 | 1 | 0 | 0 | 1800% | 1217% |