Working with payscales - IF function?

**Clippy** · 11-27-2009, 08:29 AM

This is a bit of a complicated one, or at least it feels complicated.

I am working to develop some salary grades based on the number of points roles have scored during job evaluation, for example:
Grade A = 1 - 100 points
Grade B = 101 - 200 points
etc.

What I'd like to be able to do is, as I experiment, have it so I can just adjust the number of points a grade spans so it automatically updates what grade a particular role is on. I tried using an IF function, but it didn't work as I'd hoped and also there are more than 7 grades so I don't think I could programme them all.

Excel Help suggested using v-lookup and, whilst I am familar with this function, I don't know how I'd use it in this capacity.

Any suggestions, ideas or help would be greatly appreciated, but I realise I may be trying the impossible.

Thanks

**rwgrietveld** · 11-27-2009, 08:42 AM

DonkeyOte and I just finished a similar thread http://www.excelforum.com/excel-misc...ata-range.html

**rwgrietveld** · 11-27-2009, 08:46 AM

Is this what you like?

Code:

=CHAR(FLOOR(G7,100)/100+64)

it will give you
A: 100-199
B: 200-299
C: 300-399
etc.

**Dave69rock** · 11-27-2009, 08:50 PM

Hi

maybe the attached sheet will get you started i'm sure there is a much easier way. It works with Roundup & Vlookup formula and hiding some columns.

Dave

**khamilton** · 11-27-2009, 09:37 PM

Or you could try this and you dont need column c and if you dont want #N/A replace with this :=IFERROR(LOOKUP(ROUNDUP(D6,-2),$A$1:$A$15,$B$1:$B$15),"")
Clippy(1).xls