#### formratings

##### Board Regular
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Fluff

##### MrExcel MVP, Moderator
What are the values that you want returned by the formula?

#### formratings

##### Board Regular
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

##### Board Regular
formula works if no 0's

#### Fluff

##### MrExcel MVP, Moderator

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

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

#### Fluff

##### MrExcel MVP, Moderator

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

##### Board Regular
=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

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

+Fluff.xlsm
PQAZBABBBK
434.41313329
50000
631.082828
718.871717
828.86262032
Input
Cell Formulas
RangeFormula
P4:P8P4=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:Q8Q4=AVERAGE(BA4,BK4)

#### formratings

##### Board Regular
don't understand why I am getting something different

R is the result and auto calculations are on

#### Attachments

• 1585146513437.png
2.7 KB · Views: 0

Replies
4
Views
109
Replies
1
Views
86
Replies
2
Views
138
Replies
1
Views
90
Replies
1
Views
80

1,127,894
Messages
5,627,498
Members
416,250
Latest member
darius_rebelo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back