help please

formratings

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

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
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
38,866
Office Version
365
Platform
Windows
Another option
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4,dataformlatest!BS4))
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
77
Hi sorry I wanted it to be plus BS4%
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
77
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
38,866
Office Version
365
Platform
Windows
Maybe
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+,dataformlatest!BS4))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,866
Office Version
365
Platform
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,935
Messages
5,447,400
Members
405,451
Latest member
BalbasNiBarabas

This Week's Hot Topics

Top