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
 

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"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
On the formula tab select Evaluate Formula & step through the evaluate chain. That should hopefully show where it's going wrong.
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
sorry when I said I knew very little about Excel I meant it,the evaluate thing is out of my league :confused:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
the evaluate thing is out of my league
No it's not :)
It will show you what is about to be calculated & then what the result is. That way you can see what is going on.
There is no way that I can do that from here as I do not have your workbook & cannot see what you have done.
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98

ADVERTISEMENT

I know the error now....lol
the formulas are from dataformlatest as I originally posted not the same sheet
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted & thanks for the feedback
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98

ADVERTISEMENT

not quite sorted yet :(this is formula I have now but doesn't work still
=IF(AND(dataformlatestBA4>0,dataformlatestBK4>0),AVERAGE(dataformlatestBA4,dataformlatestBK4),IF(AND(dataformlatestBA4<=0,dataformlatestBK4>0),dataformlatestBK4,IF(AND(dataformlatestBA4>0,dataformlatestBK4<=0),dataformlatestBA4,0)))*(1+BS4)
just gets me NAME error
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You are missing the ! mark between the sheet name & cell reference.
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
ok put ! in and got formula to work and it gets close but not quite correct
e.g average of 53 and 0 ( where 0 doesn't count ) is 53 + 11% is returning 61.99
when it should be 59.94
really at a loss to do something so simple :confused:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Is the percentage on the same sheet as the formula?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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