Share via

Calculation using CASE statement

Anonymous
2023-05-09T06:31:09+00:00

Hi All!

I'm struggling to come up with a formula to populate a field at work. I can use only a few Excel formulas due to the platform's limitations. Can someone help?

I can only use DATEDIFF, CASE, ADD, Subtract, Divide, and Multiply

Details following. Data that I have:

Start Date, End Date, TotalPrice

Result I need to arrive to:

Calculate the ARR (Total Price / Duration)

Considerations:

Duration cannot be 0

If StartDate is in the future (or bigger than today) ARR is zero

If EndDate is in the past, ARR is zero

If anyone has a good idea, it would be of great help!

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-12T07:55:37+00:00

    Have you tried it? Feel free to share any update.

    0 comments
  2. Anonymous
    2023-05-09T06:47:13+00:00

    Could you share some dummy data and expected result?

    =IF(OR(A2>TODAY(),B2<TODAY()),0,C2/(TODAY()-A2))

    0 comments