Nesting or joining two formulas???

[Jmcb1b]

I currently have two formulas that I'm needing to somehow join togethe
but I have not been able to do this legally.

{This formula gives a sum of any time over 8 hours and displays i
using a decimal value.}

My cells are formatted to 0### for cells C10 through F10 and cell H1
is converted to decimal using the formula below.

[this formula located in H10]
=IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100


{I still needed the above formula to display in quarter hou
increments, ie: .15 = .25, .30 = .50 etc. This was submitted to me b
JanetW and works great seperately when placed in I10}

=TRUNC(H10)+IF(MOD(H10,1)<0.15,0,IF(MOD(H10,1)<0.3,0.25,IF(MOD(H10,1)<0.45,0.5,0.75)))

My problem is that I need to incorprate both of these into one cell bu
I don't know how to go about doing it. I've tried a hundred differen
combinations with no luck. Any help is greatly appreciated and I kno
that this is probably as clear as mud so if you need more informatio
please e-mail me at (e-mail address removed) .

Thanks for all of the help. I'm very pleased with this site and th
assistance that everyone has to offer

 

[svelte]

Try =CONCATENATE(x,y) where x is one forula and y is the other (minu
the equal signs).

Is that what you needed

 

[Jmcb1b]

I'm given the Circular Reference message and haven't used cirula
reference before. Would you know what I need to do

 

[Ken Wright]

=INT(MAX((F10-E10)+(D10-C10)-800,0)/100)+ROUNDDOWN((MOD(MAX((F10-E10)+(D10-C10)-
800,0)/100,INT(MAX((F10-E10)+(D10-C10)-800,0)/100)))/0.15,0)*0.25

will match the results of your two formulas I think, but have you considered
using real times instead of lookalikes?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------

I currently have two formulas that I'm needing to somehow join together
but I have not been able to do this legally.

{This formula gives a sum of any time over 8 hours and displays it
using a decimal value.}

My cells are formatted to 0### for cells C10 through F10 and cell H10
is converted to decimal using the formula below.

[this formula located in H10]
=IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100


{I still needed the above formula to display in quarter hour
increments, ie: .15 = .25, .30 = .50 etc. This was submitted to me by
JanetW and works great seperately when placed in I10}

=TRUNC(H10)+IF(MOD(H10,1)<0.15,0,IF(MOD(H10,1)<0.3,0.25,IF(MOD(H10,1)<0.45,0.5,0
..75)))

My problem is that I need to incorprate both of these into one cell but
I don't know how to go about doing it. I've tried a hundred different
combinations with no luck. Any help is greatly appreciated and I know
that this is probably as clear as mud so if you need more information
please e-mail me at (e-mail address removed) .

Thanks for all of the help. I'm very pleased with this site and the
assistance that everyone has to offer.

 

[svelte]

Yes, but you might run out of space. Since your formula is going int
cell H10, you can't reference H10. Instead, everywhere that you woul
otherwise type "H10" you have to replace it with what's currently i
H10.

Something like this:
=Concat.((IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800
0)/100),(TRUNC((IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800
0)/100))+IF(MOD((IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800
0)/100),1)<0.15,0,IF(MOD((IF((F10-E10)+(D10-C10)>800
(F10-E10)+(D10-C10)-800
0)/100),1)<0.3,0.25,IF(MOD((IF((F10-E10)+(D10-C10)>800
(F10-E10)+(D10-C10)-800, 0)/100),1)<0.45,0.5,0.75))))


Honestly, I haven't really looked at what you're trying to do, so ther
might be a much shorter way to go about it altogether

 

[Jmcb1b]

okay , I understand. I figured that out earlier. Thanks much