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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!