Hi, I need help with the sum of dividing values. I am a little confused to explain it, so I use the following table. In general, I want to get values based on division of 4 and/or 2. In the 'Result' column are the results I want. Can I be helped by a formula so that I don't need to create a dummy table like the one in the table? Thank you
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| H | I | J | K | L | M | N | O | P | Q | |||
| 20 | Value | Divide by 4 | Whole | Decimal | Whole (Decimal) | Divide by 2 (or not) | Whole | Decimal | Whole (Decimal) | Result | ||
| 21 | 35 | 8.75 | 8 | 0.75 | 3 | 1.5 | 1 | 0.5 | 1 | 10 | ||
| 22 | 16 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | ||
| 23 | 7 | 1.75 | 1 | 0.75 | 3 | 1.5 | 1 | 0.5 | 1 | 3 | ||
| 24 | 74 | 18.5 | 18 | 0.5 | 2 | 1 | 1 | 0 | 0 | 19 | ||
| 25 | 44 | 11 | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | ||
| 26 | 51 | 12.75 | 12 | 0.75 | 3 | 1.5 | 1 | 0.5 | 1 | 14 | ||
| 27 | 67 | 16.75 | 16 | 0.75 | 3 | 1.5 | 1 | 0.5 | 1 | 18 | ||
| 28 | 16 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | ||
| 29 | 61 | 15.25 | 15 | 0.25 | 1 | 1 | 1 | 0 | 0 | 16 | ||
| 30 | 9 | 2.25 | 2 | 0.25 | 1 | 1 | 1 | 0 | 0 | 3 | ||
| 31 | 47 | 11.75 | 11 | 0.75 | 3 | 1.5 | 1 | 0.5 | 1 | 13 | ||
| 32 | 40 | 10 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | ||
| 33 | 45 | 11.25 | 11 | 0.25 | 1 | 1 | 1 | 0 | 0 | 12 | ||
| 34 | 30 | 7.5 | 7 | 0.5 | 2 | 1 | 1 | 0 | 0 | 8 | ||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I21:I34 | I21 | =H21/4 |
| J21:J34,N21:N34 | J21 | =TRUNC(I21) |
| K21:K34,O21:O34 | K21 | =I21-TRUNC(J21) |
| L21:L34 | L21 | =K21*4 |
| M21:M34 | M21 | =IF(L21>1,L21/2,L21) |
| P21:P34 | P21 | =O21*2 |
| Q21:Q34 | Q21 | =J21+N21+P21 |
