Rounding numbers or match lookup?

**smudgepost** · 06-18-2014, 06:13 AM

My survey results matches to actions called from a table and total scores per category are 0,4,8 or 16. If the survey count totals 12, I thought do do a round function to the nearest multiple to return the actions but as the numbers are stepped and not incremental, I don't know how to do this?

Any suggestions?

**Mysore** · 06-18-2014, 06:41 AM

plz upload a sample file.

**martindwilson** · 06-18-2014, 06:52 AM

what do you mean nearest multiple what should 12 return? 8 or 16
if its always the higher number
=INDEX({16,8,4,0},MATCH(A1,{16,8,4,0},-1))

**smudgepost** · 06-18-2014, 08:14 AM

Sample attached, apologies for earlier omission!

**martindwilson** · 06-18-2014, 08:27 AM

how is 12 nearer to 16 than 8?
mind you
=SUM($B$2+$E$2+$B$5+$E$5) is the same as
=SUMPRODUCT($B$2+$E$2+$B$5+$E$5,16)/SUM(16)

EDIT I GOT THAT WRONG STILL THINKING
What does 2 give 0 OR 4

**martindwilson** · 06-18-2014, 09:08 AM

ok rather than some complicated rounding thing
since you have whole numbers any way just create a table
giving what you want
eg
0 0
1 0
2 4
3 4
4 4
5 4
6 8
7 8
8 8
9 8
10 8
11 8
12 16
13 16
14 16
15 16
16 16
then you can use that

**smudgepost** · 06-18-2014, 09:25 AM

Thank you Martin. Some good ideas and good challenges. Is 2 closer to 0 or 4 is a good example. The questions in the survey omitted in attachment have complicated this for me. I'll use the match lookup for now as I can set the score to match a result as you suggest. Thank you for your input!