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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
Follow the link for XL2BB in my signature or the link in the reply window.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
Also there is a stray comma in the formula, try
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+dataformlatest!BS4))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
In that case please supply some sample data using the XL2BB add-in
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98

ADVERTISEMENT

I hope I have done this right :) I am wanting to add BS to R so 31 would become 34.41 etc
Cell Formulas
RangeFormula
R4:R22R4=IF(AVERAGE(dataformlatest!BA4,dataformlatest!BK4)<0,0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4))
BS4:BS22BS4=IFERROR( SUM( ((INDEX(Variables!$G:$G,MATCH(TRIM(D4),Variables!$F:$F,0)))/100), ((INDEX(Variables!$B$4:$B$12,MATCH(TRIM(F4),Variables!$A$4:$A$12,0))*E4)/100) ),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
=R4*(1+BS4)
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98

ADVERTISEMENT

sorry I am not explaining very well I want the result to be in R
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
In that case how about
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+BS4))
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
were getting there but its still counting 0 in the average so 2 x columns 56 and 0 + BS =11% as 30.968
when it should be 56+11% = 62.16
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
Can you please post some sample data which includes your formulas & the data they feed from.
Along with the expected results. Either I am misunderstanding you, or you are constantly shifting the goal posts.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,331
Messages
5,635,662
Members
416,871
Latest member
jbcpub

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