figure out why this formula isn't calculating correctly

Here's the formula:

=IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)

R15 = 2, R16 = 8, Q19 = 96

The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.

Here's where I think the error is occurring: the 2 and the 8 are calculated
based off the amount of time between two points during the day; 8 hours of
regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
calculated as follows:

=IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM

So, if we go back to the first formula, this is where I get lost.
Obviously, somewhere, somehow, Excel is rounding but there's nothing to
round: 8:30AM - 6:30PM is 10 hours, even.

Ideas?

--
Brevity is the soul of wit.

I calculated it as 106.

I put your second formula in R14 and result was 10. I substituted R14 for
(R15+R16) in your first formula and got 106. How do you calculate R15 & R16?

R12 & R13 are formatted as hh:mm, all others as general.

"Dave F" wrote:

> Here's the formula:
>
> =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
>
> R15 = 2, R16 = 8, Q19 = 96
>
> The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
>
> Here's where I think the error is occurring: the 2 and the 8 are calculated
> based off the amount of time between two points during the day; 8 hours of
> regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
> calculated as follows:
>
> =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
>
> So, if we go back to the first formula, this is where I get lost.
> Obviously, somewhere, somehow, Excel is rounding but there's nothing to
> round: 8:30AM - 6:30PM is 10 hours, even.
>
> Ideas?
>
> --
> Brevity is the soul of wit.

<That 10 hours is calculated as follows: >

Unfortunately the 10 is not used in your formula.
Show us how the 2, 8 and 96 are calculated.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dave F" <[email protected]> wrote in message news:[email protected]...
| Here's the formula:
|
| =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
|
| R15 = 2, R16 = 8, Q19 = 96
|
| The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
|
| Here's where I think the error is occurring: the 2 and the 8 are calculated
| based off the amount of time between two points during the day; 8 hours of
| regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
| calculated as follows:
|
| =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
|
| So, if we go back to the first formula, this is where I get lost.
| Obviously, somewhere, somehow, Excel is rounding but there's nothing to
| round: 8:30AM - 6:30PM is 10 hours, even.
|
| Ideas?
|
| --
| Brevity is the soul of wit.

R15: =IF(ISTEXT(R14),"",IF(R14>8,(R14-8),0))
R16: =IF(ISTEXT(R15),"",R14-R15)

I have R12 & R13 formatted as h:mm which I think is just military time as
opposed to the AM/PM format.

Dave

--
Brevity is the soul of wit.


"Toppers" wrote:

> I calculated it as 106.
>
> I put your second formula in R14 and result was 10. I substituted R14 for
> (R15+R16) in your first formula and got 106. How do you calculate R15 & R16?
>
> R12 & R13 are formatted as hh:mm, all others as general.
>
> "Dave F" wrote:
>
> > Here's the formula:
> >
> > =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
> >
> > R15 = 2, R16 = 8, Q19 = 96
> >
> > The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
> >
> > Here's where I think the error is occurring: the 2 and the 8 are calculated
> > based off the amount of time between two points during the day; 8 hours of
> > regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
> > calculated as follows:
> >
> > =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
> >
> > So, if we go back to the first formula, this is where I get lost.
> > Obviously, somewhere, somehow, Excel is rounding but there's nothing to
> > round: 8:30AM - 6:30PM is 10 hours, even.
> >
> > Ideas?
> >
> > --
> > Brevity is the soul of wit.

Hi Dave,

Format your cells as h:mm:ss
You'll probably discover that there were more minutes than you thought

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dave F" <[email protected]> wrote in message news:[email protected]...
| R15: =IF(ISTEXT(R14),"",IF(R14>8,(R14-8),0))
| R16: =IF(ISTEXT(R15),"",R14-R15)
|
| I have R12 & R13 formatted as h:mm which I think is just military time as
| opposed to the AM/PM format.
|
| Dave
|
| --
| Brevity is the soul of wit.
|
|
| "Toppers" wrote:
|
| > I calculated it as 106.
| >
| > I put your second formula in R14 and result was 10. I substituted R14 for
| > (R15+R16) in your first formula and got 106. How do you calculate R15 & R16?
| >
| > R12 & R13 are formatted as hh:mm, all others as general.
| >
| > "Dave F" wrote:
| >
| > > Here's the formula:
| > >
| > > =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
| > >
| > > R15 = 2, R16 = 8, Q19 = 96
| > >
| > > The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
| > >
| > > Here's where I think the error is occurring: the 2 and the 8 are calculated
| > > based off the amount of time between two points during the day; 8 hours of
| > > regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
| > > calculated as follows:
| > >
| > > =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
| > >
| > > So, if we go back to the first formula, this is where I get lost.
| > > Obviously, somewhere, somehow, Excel is rounding but there's nothing to
| > > round: 8:30AM - 6:30PM is 10 hours, even.
| > >
| > > Ideas?
| > >
| > > --
| > > Brevity is the soul of wit.

...Still OK with me, using your original formulae and adding the R15/R16 ones.

Is Q19 a constant or a formula?

"Dave F" wrote:

> R15: =IF(ISTEXT(R14),"",IF(R14>8,(R14-8),0))
> R16: =IF(ISTEXT(R15),"",R14-R15)
>
> I have R12 & R13 formatted as h:mm which I think is just military time as
> opposed to the AM/PM format.
>
> Dave
>
> --
> Brevity is the soul of wit.
>
>
> "Toppers" wrote:
>
> > I calculated it as 106.
> >
> > I put your second formula in R14 and result was 10. I substituted R14 for
> > (R15+R16) in your first formula and got 106. How do you calculate R15 & R16?
> >
> > R12 & R13 are formatted as hh:mm, all others as general.
> >
> > "Dave F" wrote:
> >
> > > Here's the formula:
> > >
> > > =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
> > >
> > > R15 = 2, R16 = 8, Q19 = 96
> > >
> > > The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
> > >
> > > Here's where I think the error is occurring: the 2 and the 8 are calculated
> > > based off the amount of time between two points during the day; 8 hours of
> > > regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
> > > calculated as follows:
> > >
> > > =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
> > >
> > > So, if we go back to the first formula, this is where I get lost.
> > > Obviously, somewhere, somehow, Excel is rounding but there's nothing to
> > > round: 8:30AM - 6:30PM is 10 hours, even.
> > >
> > > Ideas?
> > >
> > > --
> > > Brevity is the soul of wit.

Oh man do I feel like an idiot.

Q19 is the sum of cumulative hours worked. Some days a fractional number of
hours worked. It's rounding up because I don't have the decimals showing.

So all of this is calculating CORRECTLY.

Sorry for taking up your time!

Duh.
--
Brevity is the soul of wit.


"Toppers" wrote:

> ..Still OK with me, using your original formulae and adding the R15/R16 ones.
>
> Is Q19 a constant or a formula?
>
> "Dave F" wrote:
>
> > R15: =IF(ISTEXT(R14),"",IF(R14>8,(R14-8),0))
> > R16: =IF(ISTEXT(R15),"",R14-R15)
> >
> > I have R12 & R13 formatted as h:mm which I think is just military time as
> > opposed to the AM/PM format.
> >
> > Dave
> >
> > --
> > Brevity is the soul of wit.
> >
> >
> > "Toppers" wrote:
> >
> > > I calculated it as 106.
> > >
> > > I put your second formula in R14 and result was 10. I substituted R14 for
> > > (R15+R16) in your first formula and got 106. How do you calculate R15 & R16?
> > >
> > > R12 & R13 are formatted as hh:mm, all others as general.
> > >
> > > "Dave F" wrote:
> > >
> > > > Here's the formula:
> > > >
> > > > =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
> > > >
> > > > R15 = 2, R16 = 8, Q19 = 96
> > > >
> > > > The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
> > > >
> > > > Here's where I think the error is occurring: the 2 and the 8 are calculated
> > > > based off the amount of time between two points during the day; 8 hours of
> > > > regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
> > > > calculated as follows:
> > > >
> > > > =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
> > > >
> > > > So, if we go back to the first formula, this is where I get lost.
> > > > Obviously, somewhere, somehow, Excel is rounding but there's nothing to
> > > > round: 8:30AM - 6:30PM is 10 hours, even.
> > > >
> > > > Ideas?
> > > >
> > > > --
> > > > Brevity is the soul of wit.

.... it's easy done; I'm sure we have all been there but glad it's solved.

"Dave F" wrote:

> Oh man do I feel like an idiot.
>
> Q19 is the sum of cumulative hours worked. Some days a fractional number of
> hours worked. It's rounding up because I don't have the decimals showing.
>
> So all of this is calculating CORRECTLY.
>
> Sorry for taking up your time!
>
> Duh.
> --
> Brevity is the soul of wit.
>
>
> "Toppers" wrote:
>
> > ..Still OK with me, using your original formulae and adding the R15/R16 ones.
> >
> > Is Q19 a constant or a formula?
> >
> > "Dave F" wrote:
> >
> > > R15: =IF(ISTEXT(R14),"",IF(R14>8,(R14-8),0))
> > > R16: =IF(ISTEXT(R15),"",R14-R15)
> > >
> > > I have R12 & R13 formatted as h:mm which I think is just military time as
> > > opposed to the AM/PM format.
> > >
> > > Dave
> > >
> > > --
> > > Brevity is the soul of wit.
> > >
> > >
> > > "Toppers" wrote:
> > >
> > > > I calculated it as 106.
> > > >
> > > > I put your second formula in R14 and result was 10. I substituted R14 for
> > > > (R15+R16) in your first formula and got 106. How do you calculate R15 & R16?
> > > >
> > > > R12 & R13 are formatted as hh:mm, all others as general.
> > > >
> > > > "Dave F" wrote:
> > > >
> > > > > Here's the formula:
> > > > >
> > > > > =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q19)
> > > > >
> > > > > R15 = 2, R16 = 8, Q19 = 96
> > > > >
> > > > > The calculation should be 2 + 8 + 96 = 106, but it's giving me 107.
> > > > >
> > > > > Here's where I think the error is occurring: the 2 and the 8 are calculated
> > > > > based off the amount of time between two points during the day; 8 hours of
> > > > > regular and 2 hours of overtime, or 10 hours of labor. That 10 hours is
> > > > > calculated as follows:
> > > > >
> > > > > =IF(ISBLANK(R13),"",(R13-R12)*24) where R13 = 6:30PM and R12 = 8:30 AM
> > > > >
> > > > > So, if we go back to the first formula, this is where I get lost.
> > > > > Obviously, somewhere, somehow, Excel is rounding but there's nothing to
> > > > > round: 8:30AM - 6:30PM is 10 hours, even.
> > > > >
> > > > > Ideas?
> > > > >
> > > > > --
> > > > > Brevity is the soul of wit.

I noticed above that you said you changed the format to h:mm. How did you change the format to time?

Thanks,
KCain

http://www.excelsecrets.com/photoshop/index.php

If you right-click on a cell you can access custom formats via the format
cells option.

Just enter h:mm and it returns 24 hour time.
--
Brevity is the soul of wit.


"kcain" wrote:

>
> I noticed above that you said you changed the format to h:mm. How did
> you change the format to time?
>
> Thanks,
> KCain
>
> http://www.excelsecrets.com/photoshop/index.php
>
>
> --
> kcain
> ------------------------------------------------------------------------
> kcain's Profile: http://www.excelforum.com/member.php...o&userid=37907
> View this thread: http://www.excelforum.com/showthread...hreadid=574323
>
>

Thank you so much! There's still so much for me to learn about Excel!

Learn how to be a Photoshop pro!
http://www.excelsecrets.com/photoshop/index.php