in excel what is the function that enables to divide and add in multiplies of 6?

Question

I am working on a spreadsheet and I cannot add the overs column up. It gives me the on decimal instead of 6. For example 1.3 is shown as 1.5 and 1.3 add 1.5 is shown 2.8 instead of 3.2.

Answer 1

This is because you can't use a decimal point in the way you are doing.

You need two columns. One for the integers and one for the remainders (overs) which you want to show as 1/6ths.

Let the numbers you want to divide by 6 be in A, the Integer part of the division by 6 be in B and the remainder (overs) part of the division by 6 be in C.

To find the integer part of a number (in A1) divided by 6, use the formula =INT(A1/6) in B1

To find the remainder (overs) part use the formula =MOD(A1,6) in C1

You can then add these columns separately, if you wish, which will, of course, give you a number greater than 6 in the 1/6ths column if you just use the SUM function. The simplest way to arrive at the sum would be to sum the set of numbers before dividing by six (in column A) and to use the two functions as above (in columns B & C) on that sum.

If you really want to be picky and display the number 1r3 as 1.3 (instead of 1.5), you can use the TEXT function in column D as follows:

=TEXT(B1&"."&C1,"##.#")

Notice that TEXT requires a Format string as the second parameter where '#' represents a numeral 0-9. The result in column D is a text string, not a number, so you cannot perform any math on it!

Finally, it's worth noting that you CAN format a cell as Fraction, so if you have =10/6 in a cell, instead of displaying 1.6667, you can have it display as 1 2/3. What you cannot do, however, is make it display as 1 4/6.

Answer 2

You could use the time handling feature of excel if you can put up with the extra zero
Eg. for 1.3+1.5=3.2 do 1:30+1:50=3:20

If so:

Right click on the cells you want to be times.
Go to format cell and choose the custom option.
In the box by the word "type", enter [h]:mm
Enter times as h:mm and not h.mm
Use add and subtract formulae in the usual way

This will keep adding up hours so 26:50 will show as such instead of 2:50 (ie 24:00+2:50 as it rolled over 1 day).

Not ideal but an alternative if you're lazy.

Answer 3

to get the number of overs completed, eg if 1.3 overs done, converting to decimal it should be 1.5 (A2 holds 1.3, this formula goes in B2)

use this formula

=(((A2 * 10) - MOD((A2 * 10), 10)) / 10) + ((MOD(A2, 1) * 10) / 6)

to get the running total of overs, eg 1.3 and 1.5, making 3.2

use the above formula to calculate the first total, and then use it again on the second row to get the second total, then add them together. What I did was to make one column having the decimal conversion, ie, the above formula copied as such (A3 hold 1.5, this formaul goes in B3):

=(((A3 * 10) - MOD((A3 * 10), 10)) / 10) + ((MOD(A3, 1) * 10) / 6)

This will produce 1.833333 as the decimal conversion.

I made a third column, so the formulae was not too big, that combined the total so far.

In C2 I put

= B2

In C3,

=C2 + B3

The value in C3 is 3.333333

For running down the page/table, use = C3 + B4, = C4 + B5, etc

Answer 4

Create a attractiveness call CREATE. supply it a attractiveness. Any historic perceive. does not a lot challenge what perceive. So, shall we embody, "Cons" is the call. then you certainly definately write your factors like this: C1=(A1*Cons). Now drag all of it the way down to the different cells. you may desire to be certain C2=(A2*Cons), C3=(A3*Cons)

Answer 5

how do you get 3.2 by adding 1.3 and 1.5.
1.30 and 1.50 is 2.80

perhaps you are doing fractions are you.

excel sees the above as this. if you had £1.30 and £1.50 added together you have £2.80