numbers divisible by 20%

**sjc619** · 03-09-2011, 01:48 AM

Hello All -

I am trying to get my head around this and have not been able to, so hopefully someone can help me out.

I have a number is Cell F5 and I need to check to see if it is divisible by 20%

If that number is divisible by 20% I need to divide it by 20%
If it is not divisible by 20% I need to round it down to be divisible by 20% and display that value in cell G5. I know that is hard to read but I do not know how else to explain it.

Basically if I have a number in F5 - Let’s say a 5, I know that is evenly divisible by 20% so the value is 1

But if the number in F5 is let’s saying a 7, I need G5 to display the value as if it was a 5.

Thanks for the help - if it can even be done.

**JBeaucaire** · 03-09-2011, 01:58 AM

you don't mention what you expect the answer to be, but perhaps something like:

=INT(F5/0.2)

or

=INT(F5 * 0.2)

**MarvinP** · 03-09-2011, 01:59 AM

Hi sjc619 and welcome to the forum,

Divisibility deals with LCM and GCD stuff. I think you really want to know if a number is divisible by 5, based on your examples.

This means 5, 10, 15, 20 are all your lucky numbers

To trim 7 down you need to do a MOD function. That is the remainder after division. So
Mod(7,5) = 2 - In words is the remainder after dividing 7 by 5.

So you have 33 and you want to get it down to 30 so it is your 20% divisible number

You take the number 33 and subtract MOD(33,5) which is 3 or 33 - 3 = 30

So in A1 is say 54. In B2 = A1 - MOD(A1 , 5) ....... And this will come out to be 50.

I think this is what you want.

**Sadath31** · 03-09-2011, 02:02 AM

=if(mod(f5,5)<>0,int(f5/5)*5,f5)

**Sadath31** · 03-09-2011, 02:05 AM

=ROUND(F23/5,0)*5
this also work

**BlueWhale** · 11-17-2011, 02:29 PM

You can also do something like this:

= 5 * INT(F5 / 5)

**ChemistB** · 11-17-2011, 02:35 PM

or
=FLOOR(F5,5)

numbers divisible by 20%

LinkBack

Thread Tools

Rate This Thread

Display

numbers divisible by 20%

Re: numbers divisible by 20%

Re: numbers divisible by 20%

Re: numbers divisible by 20%

Re: numbers divisible by 20%

Re: numbers divisible by 20%

Re: numbers divisible by 20%

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions