Calculate lead time as a positive value if not blank
Any thoughts?
=ABS(SUMIF(Timestamp@row, <>"") - [Final Project Due Date]@row, <>"")
The following provides a positive value:
=ABS(SUM((Timestamp@row) - [Final Project Due Date]@row))
I need to find a way to exclude rows where dates were not entered so the column can be averaged.
Best Answers
-
Instead of using a SUMIF, try using an IF statement with the SUM inside of it, like so:
=IF(OR(Timestamp@row = "", [Final Project Due Date]@row = ""), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))
This way you can say that if the Timestamp or the Due Date are blank, return blank, otherwise do your calculation. Let me know if this is what you were looking to do!
Cheers,
Genevieve
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
Very close! You could either have a second IF statement instead of using AND, or you could add the statement as another OR option:
=IF(OR(Timestamp@row = "", [Final Project Due Date]@row = "", Timestamp@row > DATE(2021, 9, 28)), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))
The OR is essentially just stopping us from needing to do this, writing the IF 3 times:
=IF(Timestamp@row = "", "",
IF([Final Project Due Date]@row = "", "",
IF(Timestamp@row > DATE(2021, 9, 28)), "",
ABS(SUM(Timestamp@row - [Final Project Due Date]@row))
)))
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
Answers
-
Instead of using a SUMIF, try using an IF statement with the SUM inside of it, like so:
=IF(OR(Timestamp@row = "", [Final Project Due Date]@row = ""), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))
This way you can say that if the Timestamp or the Due Date are blank, return blank, otherwise do your calculation. Let me know if this is what you were looking to do!
Cheers,
Genevieve
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
-
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
A new wrinkle--boss needs Lead Time average to exclude records from before a certain date (so it looks better for us, of course). So I put on my "cook the books" hat and added an AND statement to exclude values from before date certain (2021, 9, 28). I was thrilled when everything lit up like a holiday, but crashed back to Earth as soon as I hit return: "Incorrect Argument." But it lit up!?! Damn you function syntax.
The Timestamp referenced is (2021, 11, 02), so it fulfills the criteria. And it lit up! I'm at a loss.
-
Very close! You could either have a second IF statement instead of using AND, or you could add the statement as another OR option:
=IF(OR(Timestamp@row = "", [Final Project Due Date]@row = "", Timestamp@row > DATE(2021, 9, 28)), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))
The OR is essentially just stopping us from needing to do this, writing the IF 3 times:
=IF(Timestamp@row = "", "",
IF([Final Project Due Date]@row = "", "",
IF(Timestamp@row > DATE(2021, 9, 28)), "",
ABS(SUM(Timestamp@row - [Final Project Due Date]@row))
)))
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.7K Get Help
- 472 Global Discussions
- 200 Use Cases
- 514 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 520 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 307 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!