cant do average, sum, count etc. based on this

[_Bigred]

(Excel 2000)

I have a field that contains the following formula: (It will check a column
H for total years of service and put the various value in "column y". So
example if employee only has 4 years seniority they get "0" for a length of
service check.
It works great for that, but when I try to set a cell to give me the average
of everyone's length of service it won't work. I have tried to do average,
count, sum etc.. on this column but can't seem to get it working).

=IF(H8<5,"0",IF(H8<10,"50",IF(H8<15,"100",IF(H8<20,"150",IF(H8<25,"200",IF(H
8<40,"250"))))))

Any ideas on how to make these work would be awesome.

TIA,
_Bigred

 

[Debra Dalgleish]

When you enclose the numbers in quotation marks (e.g. "0"), they're
treated as text, and will be ignored by SUM or AVERAGE. Remove the
quotation marks from the formula, and the other functions should work as
you expected.

 

[mikelee]

not sure what you mean by "won't work" (i.e. #NUM,
#VALUE, 0, wrong or unexpected result, etc.), but you
might try removing the quotation marks from your formula:

=IF(H8<5,0,IF(H8<10,50,IF(H8<15,100,IFH8<20,150,IF
(H8<25,200,IF(H8<40,250))))))

the quotation marks are telling excel to format the cells
as text, and you can't sum or average text (at least not
correctly if you are expecting to get the average of the
values).

hope that helps.

 

[_Bigred]

that did the trick. you're the best.

Thanks again,
_Bigred

 

[_Bigred]

a million thanks, that was dead on.

Take Care,
_Bigred

 

[Aladin Akyurek]

How about...

=IF(H8<40,VLOOKUP(H8,{0,0;5,50;10,100;15,150;20,200;25,250},2),"")

 

[J.E. McGimpsey]

Just another possible way:

=MIN((INT(H8/5))*50,250)

 

[J.E. McGimpsey]

Note the difference in results between mine and the original. The OP
didn't specify what happened if H8>=40 other than returning FALSE.
The one below returns 250. If that's an important distinction, post
back.

 

[_Bigred]

thanks guys for all the answers. I got the if statement to work. for the
purposes of table H8 WOULD NEVER be over 40 (40 was actually very generous,
so that I never had to worry about having a person have too many years of
service).

I went with the if statement formula, once the quotes were removed from
around each Length Of Service value it worked like a charm.

Thanks,
_Bigred