What is wrong with my excel formula?

Question

This formula is posted on a seperate worksheet in reference to a filtered worksheet in the same workbook
*I have to seperate the formula, otherwise Yahoo will automatically shorten it. There are NO spaces or returns in the formula

=IF(OR('For Averages'!E200:E246>=0,
'For Averages'!E200:E246<5),
AVERAGE('For Averages'!L200:L246),
-9999)

i am trying to average numbers that belong to a certain percentage (but I am not averaging the percentage) of a certain fuel type.

Answer 1

well one thing the first set of () were not closed you missed one at some point try that


you have =IF(OR( the first one does not close

Answer 2

I tried it in my excel and it worked fine, most errors are syntax, don't you hate that? Well the format should be (and don't worry I'm going to introduce spaces where you don't need them, just delete them):

=IF(OR(SUM('For Averages'!E200:E246)>=0,
(SUM('For Averages'!E200:E246)<5),
AVERAGE('For Averages'!L200:L246),
-9999)

So, if your sum is from and including 0 to and not including 5, then it will give you the value of the average of the cells you requested, otherwise it will show -9999

I'm guessing on the function you were trying to do as you actually left that out.

Answer 3

I recommend you add a column to find the percentage you are interested in, the ones between 0 and 5 (maybe you meant 0% and 5% since 5% is 0.05) (Maybe you meant AND rather than OR if you need both conditions to be true).

Then, in cell M200:
If(OR(E200>=0,E200<=5),1,0)
[This could more safely be done in 3 columns instead of one using "OR". First column would be If(E200>=0,1,0), etc.]

Then in Cell P200:
=if(M200=1,E200,"")

Copy M200 and P200 down to row 246.
Then P247 can be =average(P200:P247)

Answer 4

add me to your yahoo instant messenger and then send me the file... Maybe we can talk things out a bit better if i can see it right in front of me... I am a certified EXPERT excel MS Office User Specialist. I am willing to help you if you like...

Answer 5

For questions about Excel, I highly recommend mrexcel.com. You have to give me "Best Answer" for that one, because it is going to be super helpful to you.:) It's been to me, seriously, it's an amazing resource, post your question on their message board.

Answer 6

Why don't you start your question with what you are trying to do specifically.
Then we can look at possible revisions to your formula.

Answer 7

please re-submit the formula! placed a line spoil after each 60 characters or so... all we see is the first 60 characters of the formula: "=(G2*funds!$B$7)+('bill record'!H2*funds!$B$8)+(SUM('T-shirts'!J6... receives decrease back to you.