can I get some help here? No response yet :)

[T]

=INT(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5))+(MOD(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5),1)*0.4)

I need this to have one more column -- and have the whole formula to be able
to read negative numbers as well in all cells

Thanks
T

Was this post helpful to you?


Reply | Print post TopTop

 

[Bernie Deitrick]

T,

Nobody has any idea what you mean by "I need this to have one more column"
Formulas don't "have columns".

Perhaps a better explanation, with a sample data table and what you expect
the formula to calculate.

HTH,
Bernie
MS Excel MVP

 

[T]

Ok - Within this formula i would need to add cell "d"
what this formiula does is calculate 6+2-1.2= 6.2
6= hours plus 2 accrued - 1.2=one and a half hours used = six and half hours
I need it be able to read a negative value such as 8 +(-2) -1.2 +(-2) =4.2

 

[hrlngrv]

T wrote...

Ok - Within this formula i would need to add cell "d"
what this formiula does is calculate 6+2-1.2= 6.2
6= hours plus 2 accrued - 1.2=one and a half hours used = six and half hours
I need it be able to read a negative value such as 8 +(-2) -1.2 +(-2)

=4.2

You're not going to get there with Bernie's formula if you need to sum
over an arbitrary number of cells. If you had entries like this in
A1:A20, e.g.,

6.2
-2.2
-1
-6.2
1.3
5.2
5
8.3
7.1
7.1
7.3
4.1
-6.1
5.3
1.2
-3.1
-4
-9.3
-6
0.2

The formula

=SUMPRODUCT(SIGN(A1:A20)*(INT(ABS(A1:A20))+MOD(ABS(A1:A20),1)*2.5))
returns 22.5.

 

[T]

so if i add*.4 to the end of this formula will it produce an answer of .1
or.2 or .3 as thiese aee the increments i need the hour is split inot
quarters and these numbers designate .25 .50 and .75

 

[hrlngrv]

T wrote...

so if i add*.4 to the end of this formula will it produce an answer of ..1
or.2 or .3 as thiese aee the increments i need the hour is split inot
quarters and these numbers designate .25 .50 and .75

....

I didn't reformat the result. Thinking about it, it makes more sense to
convert back and forth between fractional formats.

For an arbitrary range, Rng, change the formula to

=--SUBSTITUTE(SUBSTITUTE(TEXT(SUM(--(SUBSTITUTE(Rng,"."," ")
&IF(MOD(Rng,1),"/4",""))),"0 0/4"),"/4","")," ",".")