|
Formatting & Formula Problem
Formula
=IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00 |
Formatting & Formula Problem
I just tested your formula with the proper results. However,
=IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00 |
Formatting & Formula Problem
Don, although your formula did work, and of course minimize any unnecessary
typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00 |
Formatting & Formula Problem
TTT Please
"Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00 |
Formatting & Formula Problem
If you type 06 15:00 in a cell, Excel will take this to be a text
string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
Yes, I understand that it is a string rather than a numeric... but that is
the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
Hi
What is the formula that is returning the value in R5 then? You say it works if you type the value in R5, but not if Excel returns the value. -- Regards Roger Govier "Titanium" wrote in message ... Yes, I understand that it is a string rather than a numeric... but that is the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
Presumably then, your value in R11 is also text, so you are trying to
compare the result of your formula (number) with a text value. If your formula returns a number like 5.75 (5 days and 18 hours), then you could wrap it in the TEXT function to convert it to text, like so: =TEXT(your_formula,"dd hh:mm") to get it in the format you want. Alternatively, you could have everything as numbers and just apply a custom format to the cells to display them as you wish - the format string is as given in the TEXT function above. When you want to enter a number like "05 15:00", enter it as "=5+15/24" (without the quotes). Hope this helps. Pete On May 29, 2:25 am, Titanium wrote: Yes, I understand that it is a string rather than a numeric... but that is the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
The formula is:
=(M5-G5)-1 M5=5/24/07 15:00 G5=5/17/07 0:00 Thanks for all your help. The next thing I could do is to create a link on my website to the spreadsheet if that would help things... "Roger Govier" wrote: Hi What is the formula that is returning the value in R5 then? You say it works if you type the value in R5, but not if Excel returns the value. -- Regards Roger Govier "Titanium" wrote in message ... Yes, I understand that it is a string rather than a numeric... but that is the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
Thanks everyone for all of your help.
I used your "=5+15/24" and it worked great. "Pete_UK" wrote: Presumably then, your value in R11 is also text, so you are trying to compare the result of your formula (number) with a text value. If your formula returns a number like 5.75 (5 days and 18 hours), then you could wrap it in the TEXT function to convert it to text, like so: =TEXT(your_formula,"dd hh:mm") to get it in the format you want. Alternatively, you could have everything as numbers and just apply a custom format to the cells to display them as you wish - the format string is as given in the TEXT function above. When you want to enter a number like "05 15:00", enter it as "=5+15/24" (without the quotes). Hope this helps. Pete On May 29, 2:25 am, Titanium wrote: Yes, I understand that it is a string rather than a numeric... but that is the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Formatting & Formula Problem
Thanks for feeding back - glad it worked for you.
Pete On May 31, 10:37 pm, Titanium wrote: Thanks everyone for all of your help. I used your "=5+15/24" and it worked great. "Pete_UK" wrote: Presumably then, your value in R11 is also text, so you are trying to compare the result of your formula (number) with a text value. If your formula returns a number like 5.75 (5 days and 18 hours), then you could wrap it in the TEXT function to convert it to text, like so: =TEXT(your_formula,"dd hh:mm") to get it in the format you want. Alternatively, you could have everything as numbers and just apply a custom format to the cells to display them as you wish - the format string is as given in the TEXT function above. When you want to enter a number like "05 15:00", enter it as "=5+15/24" (without the quotes). Hope this helps. Pete On May 29, 2:25 am, Titanium wrote: Yes, I understand that it is a string rather than a numeric... but that is the problem. When I type the string, the formula works. Which makes no logical sense to me... When the numeric data that is obtained from another formula 'does it's thing' and is then used in this formula... It doesn't work as it should. Yes, 06 15:00 is 6 days and 15:00 Hours. "Pete_UK" wrote: If you type 06 15:00 in a cell, Excel will take this to be a text string, but the comparison in your IF statement will be looking for numbers. Is your input meant to represent 6 days and 15 hours? Pete On May 29, 12:34 am, Titanium wrote: TTT Please "Titanium" wrote: Don, although your formula did work, and of course minimize any unnecessary typing, my problem still exists. I believe it may be in the formatting of the cell for some reason... When i manually type "06 15:00" the formula works. Even though the result is the same when performed via Excel calculation the result of the "IF formula" ALWAYS meets the true(5 day services) condition of the formula, never the false(Delayed). Thanks for any and all help you can provide. "Don Guillett" wrote: I just tested your formula with the proper results. However, =IF((R5<=$R$11),"5 days service",("delayed")) you don't need all the () =IF(R5<=$R$11,"5 days service","delayed") Don Guillett SalesAid Software "Titanium" wrote in message ... Formula =IF((R5<=$R$11),"5 days service",("delayed")) The cell R5 is the result of a simple formula. When the 'true' condition of the formula = True then it displays correctly. When the 'false' condition of the formula = False then it displays ONLY the true result. If the result of R5 is typed in manually, that is not obtained from a formula it will infact display as it should. R S T Service Days On Time Appt. Score in Service ROW4 05 06:00 00 01:00 5 days service ROW5 06 15:00 yes 5 days service ROW6 05 09:00 00 04:00 5 days service ROW7 05 00:00 00 01:00 5 days service R11 - 05 12:00- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com