Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Automated table after value check

Hi community, this is a though one:       I have this table composed by the departure day, some markets and the values which are a daily average of some figures. The departure date range s...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

You want to generate the below table based on the above one, right? To help us better understand your requirement, please make the logic of calculation more clear.

 

Based on my assumption, the first data table is dynamic. The highlighted data range is the expected result, which means in this period, the daily average is always over 4. In other words, each day has more than 4. But, if on one day, the average is below 4, the continual dates is broken. Then, it should be recalculate on next start day where the average is over 4 again.

 

If I get your requirement, please show us a more detailed image of your desired output, based on above sample data.

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Yuliana, 

     

    yes basically your assumption is correct. 

    the table that you reported in your post is the result. 

     

    In my mind the way this has to work is a bit long (and klunky)  as I should establish 180 if statements to make sure if the value is >4 and with another statement makes the date range.

     

    And even more complicated in makes as many lines according to the number of date ranges found. 

     

    In excel a nice macro would make this happen. don't know here >X