What could be causing inconsistent formula result?
I'm calculating HH:mm using the following logic & getting inconsistent results for the minutes calculation.
The formula is the same for all three scenarios, but the Current Year Delivery is multiplying the minutes by 100 compared to the others. All the source data is consistent. What am I missing here?
Answers
-
Hi (I cannot seem to find the name with an @ sign in it).
The reason this is happening is because the decimal point being used in the FIND is not always in the same position. With "792.250" the decimal in in the 4th position but with "94.250" it is in the 3rd position - this changes the formula.
The first 2 numbers resolve to ".25 * 60" but the 3rd resolves to "25 * 60".
A more reliable formula would be: =MID([Formula Product], FIND(".", [Formula Product]), LEN([Formula Product]) - FIND(".", [Formula Product]) + 1)
Hope this helps,
Dave
-
I answered my own question again. I was using the wrong formula. When I use the MOD function to calculate minutes/remainder it works.
Combined, here's the formula in A38:
=IFERROR(IF(INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60) < 10, "0" + INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60), INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60)) + ":" + IF(MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60) < 10, "0" + MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60), MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60)), "00:00")
-
I welcome all suggestions for simplifying.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!