Excel Questions

[mick999]

Hi There,

Just wondering if someone can help me with this excel question.

I need to write a formula to give me a score based on an achievement.

The achievement range is 80 - 100 -110 and the score range is 1 - 100 - 150.

The achievements should get the following scores,

Achiev       Score

<=80      =     1
85          =     25
90           =    50
95          =     75
100         =    100
105         =    125
>=110    =    150


Any Ideas ?

[smokeyjoe]

enter achievement score into column A, then in column B enter the formula below:
=IF(A1<=80,1,IF(A1<=85,25,IF(A1<=90,50,IF(A1<=95,75,IF(A1<=100,100,IF(A1<=105,125,IF(A1>105,150)))))))
Should work

[mylestheslasher]

Can you get a score of 96 for example?

If you can only get the scores you have listed you could set that up as a table and use a Vlookup to find the achievment and return the corresponding score.

Other way is to use a nested IF formula - If (Achieve<80, 1, (another nested IF)) - thats a bit messy though.

Does that help?

[armaghniac]

 

If you can only get the scores you have listed you could set that up as a table and use a Vlookup to find the achievment and return the corresponding score.

Vlookup is the job for this problem. It will work with the ranges e.g. 85-90 gives 25, if they are in sorted order.

[mick999]

Thanks Lads for the suggestions ..

My problem is that I can have any value in between the values that I have suggested as well ..

Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..

[Banana Man]

lads is there anywhere i can get excel downloaded for free or cheap for my home laptop???

[Billys Boots]

Are there a limited number of scores - is it just as the first list suggests?  It should be possible to nest seven IFs, otherwise use the Vlookup.

[imtommygunn]

Your solution needs to be "two pronged" so:

1. Get the lower and upper range.
2. Get the average points increase per achievement increase.
3. Subtract the lower range from your score.
4. Multiply your score by the average points increase per achievement.
5. Add on the lower range points.

The above could maybe be done with vlookup and then formula on top of it. Not familiar enough with excel to know.

[Maiden1]

Thanks Lads for the suggestions ..

My problem is that I can have any value in between the values that I have suggested as well ..

Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..

Provided A1 has a value then

=IF(A1<=80,1,IF(A1>=110,150,(A1-80)*5))

Will give you the correct value

[mylestheslasher]

lads is there anywhere i can get excel downloaded for free or cheap for my home laptop???

Piratebay

[mick999]

Thanks Lads for the suggestions ..

My problem is that I can have any value in between the values that I have suggested as well ..

Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..

Provided A1 has a value then

=IF(A1<=80,1,IF(A1>=110,150,(A1-80)*5))

Will give you the correct value

Thanks a Mill, That sorted it ..

[Banana Man]

lads is there anywhere i can get excel downloaded for free or cheap for my home laptop???

Piratebay

cheers myles

[armaghniac]

Openoffice is effective enough for reading and using Excel files, and its free.

The other cheap source of MSOffice is a student licence, if you have any class of scholar in your dwelling.

[Banana Man]

Openoffice is effective enough for reading and using Excel files, and its free.

The other cheap source of MSOffice is a student licence, if you have any class of scholar in your dwelling.

good man cheers

[RMDrive]

We recently moved to Office 2007 at work and today I used its conditional formatting function for the first time. Nice! It way easier to use than 2003 and it can automatically add colour codes, traffic lights etc to your data, as well as loads of cool stuff like filling the cell with a colour.
Excel is the business.