Solved Returning a value from the top of a table

Microsoft Excel 2007
February 13, 2013 at 18:36:25
Specs: Windows XP
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.

See More: Returning a value from the top of a table

Report •


#1
February 13, 2013 at 19:50:27
✔ Best Answer
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	X		

If 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	X

with 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..


Report •

#2
February 15, 2013 at 19:20:27
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.


Report •

#3
February 15, 2013 at 20:03:20
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.


Report •

Related Solutions

#4
February 15, 2013 at 20:43:25
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?

Report •


Ask Question