Formula to round down on a batch and exclude anything under a full batch

**toomuchbrew** · 08-15-2012, 10:31 AM

This is my first post and I really don't know what the function is called or even if it is possible, so I am not really able to do a search.. Please be open-minded at my noobness.

I am trying to make a formula that will exclude any number other than a full batch count. For example
My batch is 333 and every one batch = 1000. So I would like my formula to = the output for the number of full batches in increments of 333.

Simplified:
a = 1000/333 where a is the actual input

if a is 333, then the output is 1000
if a is 666 (coincidence) than the output is 2000
if a is 331 the output is zero
and if a is 334-665 the output is 1000

any ideas? I am wracking my brain over this to find an exclusion logical statement. I know where i need to be, just not what it's called or how to get there.

**NBVC** · 08-15-2012, 10:37 AM

Just a guess based on my simple understanding...

try formula:

=1000*FLOOR(A1,333)/333

where A1 contains a value like 333

edit: Changed function to FLOOR... for rounding down

**toomuchbrew** · 08-15-2012, 11:01 AM

Hrm.. I just happen to have OpenOffice here at home. I am getting errors on the formula so I am not sure if it is me or OO.

Any experience with (don't hit me) OpenOffice?

**NBVC** · 08-15-2012, 11:02 AM

I am not sure if OO has the FLOOR function, but I know that you need semi-colons as separators, not commas.. try:

=1000*FLOOR(A1;333)/333

**toomuchbrew** · 08-15-2012, 11:04 AM

Originally Posted by toomuchbrew

Hrm.. I just happen to have OpenOffice here at home. I am getting errors on the formula so I am not sure if it is me or OO.

Any experience with (don't hit me) OpenOffice?

Disregard, I had to change the "," to a ";" and it works. Thanks!!