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

  • Thread starter Thread starter _Bigred
  • Start date Start date
B

_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
 
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.
 
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.
 
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.
 
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
 
Back
Top