Round to an integer

[tara_922]

Hi everybody!

I'm working on a spreadsheet where users have to choose either a -1 or a +1. 0 is not an option. But when averaging the answers excel is calculating in 0 (as it should). However, I would like to round to an integer. Is this possible? If so how?

Thanks in advance!

Tara

 

[shg]

Zero is an integer ...

Can you give an example?

 

[Sal Paradise]

Something like this?

=IF(ROUND(A1,0)=0,LOOKUP(A1,{-1,-1;0,1}),ROUND(A1,0))

 

[barry houdini]

If you are averaging just -1s and 1s then if you don't want a zero you want the closest to 1 or -1 right?

=IF(SUM(range)<0,-1,1)

 

[Scott Huish]

I'm guessing you want to exclude zeros from your average, try this:

=AVERAGE(IF(A1:A100,A1:A100))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[mikerickson]

Perhaps this CSE formula
=2 * (ROUND(AVERAGE((0.5+(A1:A5/2))), 0) - 0.5)

The logic is
1) covert the -1 and 1 to 0 and 1 with (A1/2) + .5
2) Average those
3) Round the result to 0 or 1
4) convert back to -1/1 with 2 * (ROUND()-.5)

 

[tara_922]

You all gave me some excellent options! Thanks so much!

Tara

 

[tara_922]

=AVERAGE(IF(A1:A100,A1:A100))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

This one didn't work (I chose it first as it seemed the simplest), still returned 0 even though data included no 0's. Thanks for your time!

 

[barry houdini]

Hello tara_922,

You aren't giving much away. Can you be more specific, please give some examples of the exact data you have and the results you would like to get, thank you

 

[vba_john]

Hi

I think I understand what you're trying to do. The reason why its returning 0 is because the average of -1 and 1 is 0. If there are more -1s than 1s (-1,-1,-1,1,1) then the average will be something like -0.2 which will round to 0. You need to use ROUNDUP to get it to -1 or 1. Also, if there are 0s in the dataset that you don't wish to count, then AVERAGEIF does the trick.

Try this:

=ROUNDUP(AVERAGEIF(A:A,"<>0",A1:A100),0)