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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Hey,

So would you want to return 0 for the average if AVERAGE(BA4,BS4,BK4) < 0 ? otherwise return their average? is that right?

If so then the formula would be:

=IF(AVERAGE(dataformlatest!BA4,dataformlatest!BK4,dataformlatest!BS4)<0,0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4,dataformlatest!BS4))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,555
Office Version
  1. 365
Platform
  1. Windows
Another option
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4,dataformlatest!BS4))
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98

ADVERTISEMENT

Hi sorry I wanted it to be plus BS4%
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
return the average of BA and BK and if 0 for either doesn't count and then plus BS %
so average in this case is 20.7 plus BS% which is 11% = 22.97
 

Fluff

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

ADVERTISEMENT

Maybe
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+,dataformlatest!BS4))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,555
Office Version
  1. 365
Platform
  1. Windows
In what way?
Saying "It didn't work" is not very helpful ;)

Can you please post some sample data using the XL2BB add-in?
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
sorry don't know what XL2BB is this is what I get
1585065345289.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,555
Messages
5,637,016
Members
416,954
Latest member
Gohar hussain

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