Isolating particular 3 digit numbers in excel

A

apple2357

Full Member
Joined
Mar 9, 2018
Messages
520
I am trying to solve a problem that i want to check on excel.

The problem is trying to find the sum of all 3 digit numbers that do not contain a zero (e.g 357 is ok but 205, 230 etc is not). I think i have an answer but i would like to play with the sequence in excel and check it.

I can't seem to find a way of excluding these numbers before i sum it? I normally use modular arithmetic to do this and i can exclude the numbers with 0 at the end but i don't know how to cope with the ones with zero in the middle?

Any thoughts?
 
apple2357 said:
I am trying to solve a problem that i want to check on excel.

The problem is trying to find the sum of all 3 digit numbers that do not contain a zero (e.g 357 is ok but 205, 230 etc is not). I think i have an answer but i would like to play with the sequence in excel and check it.

I can't seem to find a way of excluding these numbers before i sum it? I normally use modular arithmetic to do this and i can exclude the numbers with 0 at the end but i don't know how to cope with the ones with zero in the middle?

Any thoughts?
Click to expand...

List your 3 digit numbers in column A.

In Column B have the function

=IF(OR(MOD(A1,10)=0,MOD(FLOOR(A1/10,1),10)=0,MOD(FLOOR(A1/100,1),10)=0),0,A1)

where A1 is replaced by the appropriate row number of column A.

Excel does this for you automatically if you write the above in the first row and copy or drag fill it to the 900th row.
 
Romsek said:
List your 3 digit numbers in column A.

In Column B have the function

=IF(OR(MOD(A1,10)=0,MOD(FLOOR(A1/10,1),10)=0,MOD(FLOOR(A1/100,1),10)=0),0,A1)

where A1 is replaced by the appropriate row number of column A.

Excel does this for you automatically if you write the above in the first row and copy or drag fill it to the 900th row.
Click to expand...

Wow. That was quick. Let me try that!

Edit: 2 mins later

Just tried it! It does work. Thank you.
Can you offer me any thoughts on what the floor function is doing here? I am curious to understand it for the future
 
Last edited:
apple2357 said:
Wow. That was quick. Let me try that!

Edit: 2 mins later

Just tried it! It does work. Thank you.
Can you offer me any thoughts on what the floor function is doing here? I am curious to understand it for the future
Click to expand...

Floor(x) is the largest integer <= x

Floor(1.4) = 1
Floor(-1.4)=-2

Excel adds a parameter called significance which let's you find the largest multiple of the significance less than x.
I just set this to 1 to obtain the plain Floor function.
 
You must log in or register to reply here.
Top