1 number divisible by group of numbers [Excel?]

marty

1 MW
Joined
Apr 19, 2007
Messages
2,810
Location
Buffalo, New York USA
Want 1 number that divides equally into the following 4 numbers:
25.50
51.37
53.81
77.75
The one number I am dreaming of is 6, or any number between 5 and 7. Six would be best.
If that don't work, I also like the number 10, or any number between 9 and 11. Ten would be best.
Before I blow a fuse in my mind, can a computer figure this out? Spread sheet?

Am I having delusions and asking for for the impossible? It's hot here and I might be having heat stroke?
 
Hey Marty =

Could you describe a bit more clearly exactly what you are looking for?

For instance, 6 into 25.5 gives 4.25, and just over double that for the next number, (8.551) but no clean fit for the next number after that. That's top of my head calcs, need more info.

But, I don't know what you are looking to achieve. No simple common factors with precision.
 
Excel will only do the math if you write the formula.

What you seem to be asking is ridiculous. Maybe we'd understand better if you said what it's for.

25.5, 51, 76.5. With that 153 would divide by 2,, 3 and 4 to get your numbers. But talking about 6 and 10 sounds like you don't want to "Divide into," but to, er. . . ?

Just a hunch, I'll bet you're looking for something around 5.1, but I don't believe that there's one number for all 4.
 
Ceramic tile on the walls in a kitchen. At the top of each wall will be a horizontal border made from marble. I can cut pieces of marble to any size. Hence the numbers around 6 or 10.

Made a spreadsheet. Row 1 is the dimension of each wall. These numbers are fixed and can not change. Column A is options for the pieces of marble that I will cut. There are 201 numbers ranging from 5.00 to 7.00 in units of .01 Please see screen shot of top of spread sheet. Note that real spread sheet goes down 200 rows. Be happy to share .ods file if anyone likes.

Will make a second spread sheet comparing all the numbers between 9.00 and 11.00 in units of .01

1_num_div_grp.01.png
Next step, I am having trouble describing with words. See the groups of 4 numbers in each row? Numbers in first row are:
5.10, 10.27, 10.76, 15.55
Need these numbers to all be as close to whole numbers as possible. Example 5 is best. 4.99 is OK. 5.01 is OK. 5.5 is horrible.

How to make a spread sheet formula in column F to compare all 201 rows to find which one has numbers closest to whole numbers. Another name for whole numbers is Integer.
https://en.wikipedia.org/wiki/Integer

Thanks for the replies!
 
Sounds like the solver function might be most useful?
 
I figured it was something like that.

The way this is usually done is to use pieces of uniform size, then either make one very different row or column for correction, which can be used to artistic effect, you can break the correction row into several, but likely different numbers of rows or columns for each wall.

If you want to hide the correction, then you will need multiple rows with very small variances.

Example - 5 inch tile, 47 inch wall, use 9 regular tiles and one two inch tile, or 8 tiles normal and two at 3.5, etc, all the way down to cutting all 10 tiles down to 4.7 inch.

You can do this in symetrical rows and columns or randomize for some variety. Much of this can be done with slightly wider grout lines.

Another is what is called an accent piece, a small trim item used as filler. Cutting lots of pieces of ceramic really sucks.
 
I think you need to factorise your four wall length numbers and then see if there's one they all share that's a sensible length for a tile i.e. not 0.1" or 5ft.

However I don't see it working perfectly. By the time you've stacked up cutting tolerance and spacing tolerance (even with using spacers) it's unlikely the last time is going to fit exactly so you still end up cutting an odd piece to fit.

Why not just start in the middle of each wall and have two equal sized cut-down pieces on each end when you reach the corners? For neatness you could cut your marble to match the length of the ceramic tiles (or a multiple thereof) so the grout lines for each align.
 
what you could to next to the sheet you have is the following formula. =number-TRUNC(number)

this will give you the decimal part for the cell. next do the formulla =IF(number<=0.01,"OK",IF(number>=0.99,"OK"," "))

in this case, 'number' will be the one of the cells from your four calculated columns the 0.01 and 0.99 is the values you are looking for



so first make four helper columns with the first formula, then four columns with the second formula but on the values of the helper columns.

make a conditional formatting rule to make a cell green for "OK".

last task is to look for four green cells in a row.

EDIT:
i made the spreadsheet. see attachement

cell A1 holds a value you want to max deviate from the whole number

i made this in excel 2010 and saved as .ods. hopefully it works for you
 

Attachments

  • for_marty.ods
    145 KB · Views: 25
Nieles,
Wow! Thanks for the spread sheet! Them formulas are way more then I am understanding. Match up the 4, OKs. About 8.76 seems to be your magic number. Made a drawing.
8.76.01.png
Here is another way to look at spreadsheet.
1_num_div_grp.02.png
Different options:
25.50 / 5 = 5.1
51.37 / 10 = 5.14
53.81 / 10 = 5.38
77.75 / 15 = 5.18
or
25.50 / 4 = 6.37
51.37 / 8 = 6.42
53.81 / 8 = 6.73
77.75 / 12 = 6.48
or
25.50 / 3 = 8.5
51.37 / 6 = 8.56
53.81 / 6 = 8.97
77.75 / 9 = 8.64

As of today I am liking 6.37" to 6.73" pieces of marble.
 
Back
Top