Calculation solution required if a cell is NUL

[ateeqsindhu]

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%

 

[Joe4]

So which formula is problematic?
Can you walk us through an example, using the data you posted, telling us what is happening, and exactly what you would like to happen instead?

 

[ateeqsindhu]

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%

Column 1 contains data of CR Limit and Column 2 contain data of DR Limit of an item. this is to be compared with column 3 to 6 in percentage% to assess how much over / under that item performed. Column 3 and 4 are data of 12 months of 2021 and Column 5 & 6 are data of 6 months of 2022, hence total period under review is 18 months.
Colum 3 & 5 are to be compared with Column 2 and Column 4 & 6 are to be compared with column 1
Under Column 7 i've counted data appearances in column 3 to 6 so in 1st row it is 4 as all columns (3 t o6) contain data and in row no3 it is 2 as only column 5& 6 contain data and so on.

REQUIREMENT: have to calculate actual performance in comparison with Limits if all 4 columns have data then its ok but when its only 2 columns have the data then it is complex, as the data appearing might be under column 3 or 4 (making it data for 12 months) OR 5 or 6 (making it data for 6 months).

PROBLEM

a) single furmula whether data in column 3 & 5 OR 4 & 6 is available or any one missing / NUL same should be / the figure should be averaged and them comparison with either column 1 or 2 be made to achieve %

 

[Joe4]

Please walk us through actual examples from your data, showing us how you arrived at the expected result for each situation.
Be sure to include an example from each situation (i.e. data in all 4 columns, data only in columns 5 & 6, etc).

I want to see the actual value you want back, so we can work with your sample data to see how you arrive at that value for each situation.