help please

[formratings]

SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED
this formula is in a sheet called ignore
=IF(AVERAGE(dataformlatest!BA4,dataformlatest!BK4)<0,0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4,))
and runs down a full column,BA4,BA5,BA6 which has a different value in each etc how do I add the value of another
ignore column BS4,BS5,BS6 etc to it which has different values in it
many thanks

 

[formratings]

ok will do basically want the average of 2 columns where if 0 it doesn't count in the average

1.xlsm
	AJ																									BI
4	0																									0
5	0																									0
6	56																									0
7	34																									0
8	56																									0
9	56																									0
10	38																									0
11	71																									0
12	30																									0
13	29																									0
14	0																									0
15	29																									0
16	38																									0
17	36																									0
18	37																									0
19	26																									0
20	55																									0
21	37																									0
22	30																									0
23
dataformlatest

1.xlsm
	CQ
4	0
5	0
6	30.968
7	18.802
8	30.968
9	30.968
10	21.014
11	39.263
12	16.59
13	16.037
14	0
15	16.037
16	21.014
17	19.908
18	20.461
19	14.378
20	30.415
21	20.461
22	16.59
ignore

Cell Formulas
Range	Formula
CQ4:CQ22	CQ4	=MAX(0,AVERAGE(dataformlatest!AJ4,dataformlatest!BI4)*(1+BS4))

 

[Fluff]

What are the values that you want returned by the formula?

 

[formratings]

BS changes per section but in this section it is 11%, so row 6 would be average of 56 + 0 ( because then 0 doesn't count in average)
sum would be 56+11% = 62.16 not 30.968 which it would be if 0 was counting in the calculation

 

[formratings]

formula works if no 0's

 

[Fluff]

I'm sorry but I still don't understand.
You asked for a value to be add to your original formula, but your formula returns 28 for row6, not 56. So are you saying that your formula does not return the correct value?

 

[formratings]

yes that's right my formula doesn't return the correct value,

 

[Fluff]

It would have been helpful if you had said that right from the start.

Try something like
=IF(AND(BA4>0,BK4>0),AVERAGE(BA4,BK4),IF(AND(BA4<=0,BK4>0),BK4,IF(AND(BA4>0,BK4<=0),BA4,0)))*(1+BS4)

 

[formratings]

=IF(AND(BA4>0,BK4>0),AVERAGE(BA4,BK4),IF(AND(BA4<=0,BK4>0),BK4,IF(AND(BA4>0,BK4<=0),BA4,0)))*(1+BS4)

where BA4 is 33 and BK is 29 +11% the formula above gives me 61.9 when it should be 34.41
I'm sorry for being a pain in the butt

 

[Fluff]

Not if you've entered it correctly it doesn't
It's only adding the % to the result, not to BK

+Fluff.xlsm
	P	Q																																			AZ	BA	BB									BK
4	34.41	31																																				33										29
5	0	0																																				0										0
6	31.08	28																																				28
7	18.87	17																																				17
8	28.86	26																																				20										32
Input

Cell Formulas
Range	Formula
P4:P8	P4	=IF(AND(BA4>0,BK4>0),AVERAGE(BA4,BK4),IF(AND(BA4<=0,BK4>0),BK4,IF(AND(BA4>0,BK4<=0),BA4,0)))*(1+BS4)
Q4:Q8	Q4	=AVERAGE(BA4,BK4)

 

[formratings]

don't understand why I am getting something different

R is the result and auto calculations are on