I have an Excel spreadsheet with five columns, the number of rows can be infinite after the top row which consists of . Column A=Hours, B=Score, C=X, D=Y and E=Z. I would like to be able to enter hours into column A and have ther score of X,Y, Z or a dash (-) self populate. If the number of hours is less than 20 the score should be a dash, between 20-30 the score would be X, 30-40 =Y, over 40=Z. It seems to me that this shouldn't as much trouble as I'm having but I just can't figure it out. Any help would be greatly appreciated.

Is this what you are trying to get Hours X Y Z 15 - 20 - 25 Y 30 Y 35 X 40 X 20 - 25 Y 30 Y 35 X 40 XIf so then the calcs would be in B2,C2,D2

=IF(A2<=20,"",IF(A2<=30,"",IF(A2<=40,"X","")))

=IF(A2<=20,"",IF(A2<=30,"Y",IF(A2<=40,"","")))

=IF(A2<=20,"-",IF(A2<=30,"",IF(A2<=40,"","")))Or you can get it all in 1 column like this

Hours Score 15 - 20 - 25 Y 30 Y 35 X 40 X 20 - 25 Y 30 Y 35 X 40 Xwith this calc

=IF(A2<=20,"-",IF(A2<=30,"Y",IF(A2<=40,"X","")))

this assumes your hours are in number format as opposed to time format and that the demarcations between scores is as you want them (the <= in the calcs) and that no-one ever works more than 40 hours..

Altek, Thanks for the reply, that works but what if I throw in this kink. If we added a rating column and A, B, or C will be the value. Depending on the rating the scores for X, Y, Z will vary. For instance the value of X would be 30-40 for A, 25-35 for B and 20-30 for C. The scores for Y and Z would follow in the same manner.

re: For instance the value of X would be 30-40 for A, 25-35 for B and 20-30 for C.So any number from 30 to 35 could be either A or B?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, dependant upon the rating. I just solved it by having a table feed the XYZ cells according to the rating. Then the score is determined by where the hours fall with in those cells and returns the XY or Z. Far more complicated then I had hoped and I'm betting theres an an equally complicated formula that will do the same thing. But by having the XYZ column fed from seperate table I think it will be easier to update if those numbers change. Confused?

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History