help please

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
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

Board Regular
Joined
Apr 5, 2015
Messages
98

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
What are the values that you want returned by the formula?
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 5, 2015
Messages
98
=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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 5, 2015
Messages
98
don't understand why I am getting something different
1585146564593.png

R is the result and auto calculations are on
1585146651258.png

1585146737051.png
 

Attachments

  • 1585146513437.png
    1585146513437.png
    2.7 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top