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 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
Also there is a stray comma in the formula, try
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+dataformlatest!BS4))

formratings

Board Regular
thanks that formula works without error but gives wrong output of 3441 instead of 22.9

Fluff

MrExcel MVP, Moderator
In that case please supply some sample data using the XL2BB add-in

formratings

Board Regular

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) ),"")

=R4*(1+BS4)

formratings

Board Regular

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

Fluff

MrExcel MVP, Moderator
=MAX(0,AVERAGE(dataformlatest!BA4,dataformlatest!BK4)*(1+BS4))

formratings

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

Replies
4
Views
112
Replies
1
Views
119
Replies
2
Views
155
Replies
1
Views
93
Replies
1
Views
100

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.

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