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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!