Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
Here's the formula:
=IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q1 9) 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
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)+Q1 9) 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
<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" wrote in message ... | Here's the formula: | | =IF(OR(ISTEXT(R15),ISTEXT(R16)),0+Q19,(R16+R15)+Q1 9) | | 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
R15: =IF(ISTEXT(R14),"",IF(R148,(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)+Q1 9) 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
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" wrote in message ... | R15: =IF(ISTEXT(R14),"",IF(R148,(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)+Q1 9) | | 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
...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(R148,(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)+Q1 9) 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
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(R148,(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)+Q1 9) 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
.... 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(R148,(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)+Q1 9) 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
figure out why this formula isn't calculating correctly
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekday formula calculating to end of month | Excel Worksheet Functions | |||
CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES | Excel Worksheet Functions | |||
formula returns (correctly) a negative value | Excel Discussion (Misc queries) | |||
Formula for calculating age | Excel Discussion (Misc queries) | |||
How do figure excel formula out? | Excel Discussion (Misc queries) |