Hoping someone can help me out with this as I really have no idea where to start. I'm trying to make a score system so that:
Scores greater than 100 = 1 point
Scores between 80 and 99 = 2 points
Scores less than 80 = 3 points
Attached is what I have so far. I don't know how to nest if functions. I hope I explained it well enough. I'm thinking I might create a separate worksheet with a grid of possible outcomes, but I know there's an easier way than that.
Any help would be greatly appreciated!!
=LOOKUP(a1,{0,80,100},{3,2,1})
i take it you mean 100 and greater?
you need to define you boundaries what would 99.5 get?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I tried
=LOOKUP(B12,{<80,>=80 &<99, >100},{3,2,1}) but it didn't work.
Martin, 99.5 would get a 1... I would round up. I'm going to change it so that there are only 2 decimal places.
round how?
round() or roundup()
99 -- 99 -- 99
99.1 -- 99 -- 100
99.2 -- 99 -- 100
99.3 -- 99 -- 100
99.4 -- 99 -- 100
99.5 -- 100 -- 100
99.6 -- 100 -- 100
99.7 -- 100 -- 100
99.8 -- 100 -- 100
99.9 -- 100 -- 100
100 -- 100 -- 100
Last edited by martindwilson; 02-10-2014 at 10:12 PM.
I'm unfamiliar with round() and roundup(), but I suppose anything .5 and above would be rounded up. Is that enough? Sorry, I know I'm being pretty useless right now - long day.
ok that would be round
so round(a1,0)
=LOOKUP(ROUND(A1,0),{0,80,100},{3,2,1}) would give these results see attached
but you can just change the lookup boundary to to include all those over 99.5
Last edited by martindwilson; 02-10-2014 at 10:32 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks