**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi I have a column which gives me daily temperatures since 1975 till 2017. I am trying to calculate the cumulative deviation of temperature and trigger a payout if the cumulative is greater than 150 degrees celcius or 250 degrees. The deviation is based on 'triggers' so if the daily max temp is 30 and the trigger is 31 then the cumulative deviation is -1 and so on. I have a set of 6 triggers - 31, 33 35.5 38 39.5 and 39. How can I calculate this on excel?

Solved by O. C. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
04/09/2018 - 01:38

Hello

User
04/09/2018 - 01:38

hi

Excelchat Expert
04/09/2018 - 01:38

How are you today?

User
04/09/2018 - 01:38

ok

Excelchat Expert
04/09/2018 - 01:38

Nice to hear that

Excelchat Expert
04/09/2018 - 01:38

Can you please describe more on the problem!

User
04/09/2018 - 01:39

sure so given the description above do you have additional questions

Excelchat Expert
04/09/2018 - 01:39

Yes, I can help you to implement in excel but I need to understand what is cumulative deviation?

Excelchat Expert
04/09/2018 - 01:39

and if you can provide the sample data and sample output

Excelchat Expert
04/09/2018 - 01:40

it will help me to understand the problem better

User
04/09/2018 - 01:40

if i have a column of maximum daily temperatures and some triggers (e.g 1st mar to 31st mar the trigger is 31 degrees and 1t April to 30th apr the trigger is 33 degrees).

Excelchat Expert
04/09/2018 - 01:41

Ok

User
04/09/2018 - 01:41

then the cumulative deviation would be the sum of daily differences from the respective triggers

User
04/09/2018 - 01:41

daily differences in max tem that is

Excelchat Expert
04/09/2018 - 01:42

Ok. So it will be the sum of daily difference of the max temperature from the respective trigger

User
04/09/2018 - 01:42

yes

Excelchat Expert
04/09/2018 - 01:42

and the period would be?

User
04/09/2018 - 01:42

the problem is the trigers vary

User
04/09/2018 - 01:42

and I have a lot of daily data since 1975 to now

User
04/09/2018 - 01:42

riggers vary across the year every year

Excelchat Expert
04/09/2018 - 01:42

Ok

Excelchat Expert
04/09/2018 - 01:43

Is it possible for you to share the sample data or build some sample data on the right?

User
04/09/2018 - 01:46

and so on Have this data for thirty years

Excelchat Expert
04/09/2018 - 01:47

And cumulative deviation will keep on adding day by day

Excelchat Expert
04/09/2018 - 01:47

is that correct?

User
04/09/2018 - 01:47

only for the window defined in the trigger

Excelchat Expert
04/09/2018 - 01:47

Ok

Excelchat Expert
04/09/2018 - 01:47

and after that it will again start again

Excelchat Expert
04/09/2018 - 01:47

for the new period

User
04/09/2018 - 01:48

yes with a new trigger

Excelchat Expert
04/09/2018 - 01:48

Ok

Excelchat Expert
04/09/2018 - 01:48

o that means we will have 1 cumulative deviation for 1 period

Excelchat Expert
04/09/2018 - 01:48

I understand

Excelchat Expert
04/09/2018 - 01:48

Now this trigger is in this format only?

User
04/09/2018 - 01:48

yes

Excelchat Expert
04/09/2018 - 01:48

because years are not mentioned in the triggers

User
04/09/2018 - 01:49

no this is irrespectiv eof year so it just repeats

User
04/09/2018 - 01:49

it aries within the year not across years

User
04/09/2018 - 01:49

varies

Excelchat Expert
04/09/2018 - 01:49

Ok. So we have 24 triggers in total

User
04/09/2018 - 01:49

well yes for this sample data we will have 24 triggers

Excelchat Expert
04/09/2018 - 01:50

ok

Excelchat Expert
04/09/2018 - 01:51

We need to arraneg the triggers in this format first

User
04/09/2018 - 01:52

ok

User
04/09/2018 - 01:56

hi

User
04/09/2018 - 01:56

are you still there

Excelchat Expert
04/09/2018 - 01:57

Yes, building the formula for you

User
04/09/2018 - 01:57

ok thanks

Excelchat Expert
04/09/2018 - 01:58

The we build this If formula

Excelchat Expert
04/09/2018 - 01:58

Can you see the formula in Column C

User
04/09/2018 - 01:58

no

User
04/09/2018 - 01:59

can you type it here in the message

Excelchat Expert
04/09/2018 - 01:59

=if(and(A2>=$G$3,A2<=$H$3),$I$3,If(and(A2>=$G$4,A2<=$H$4),$I$4,if(and(A2>=$G$5,A2<=$H$5),$I$5,"")))

Excelchat Expert
04/09/2018 - 01:59

so getting the values of respective trigger

Excelchat Expert
04/09/2018 - 02:00

If you will go down to row 76, you can see the value coming in for the period

User
04/09/2018 - 02:00

I it varying the trigger by day

Excelchat Expert
04/09/2018 - 02:01

I though the trigger are varying by fortnight

Excelchat Expert
04/09/2018 - 02:01

like 16 Mar to 31 Mar - 31

Excelchat Expert
04/09/2018 - 02:01

Like that

User
04/09/2018 - 02:01

yes but the formula seems to be shifting on each cell

Excelchat Expert
04/09/2018 - 02:02

Yes, because

Excelchat Expert
04/09/2018 - 02:02

we need to calculate for each day

Excelchat Expert
04/09/2018 - 02:02

like go down to row 76

Excelchat Expert
04/09/2018 - 02:03

On cell reference C76 you can see 31

Excelchat Expert
04/09/2018 - 02:03

Triggers are coming automatically

User
04/09/2018 - 02:04

I see

Excelchat Expert
04/09/2018 - 02:04

So deviation = trigger - temp

Excelchat Expert
04/09/2018 - 02:04

or temp - trigger?

User
04/09/2018 - 02:05

trigger - temp

Excelchat Expert
04/09/2018 - 02:05

Ok

Excelchat Expert
04/09/2018 - 02:05

Now you can see the deviation coming up for each day

User
04/09/2018 - 02:06

yes great

Excelchat Expert
04/09/2018 - 02:06

Now in next column we need to add the cumulative deviation

User
04/09/2018 - 02:06

If I wanted to add more conditions to this can I?

Excelchat Expert
04/09/2018 - 02:06

we have to add more If and

Excelchat Expert
04/09/2018 - 02:06

Let me explain that

Excelchat Expert
04/09/2018 - 02:07

=if(and(logical test 1, logical test 2),Value if True, Value if false)

Excelchat Expert
04/09/2018 - 02:07

this is basic If and function

Excelchat Expert
04/09/2018 - 02:08

=if(and(A2>=$G$3,A2<=$H$3),$I$3,If(and(A2>=$G$4,A2<=$H$4),$I$4,if(and(A2>=$G$5,A2<=$H$5),$I$5,""))

Excelchat Expert
04/09/2018 - 02:08

Let's understand this

Excelchat Expert
04/09/2018 - 02:08

Are you with me?

User
04/09/2018 - 02:08

yes

Excelchat Expert
04/09/2018 - 02:08

ok

Excelchat Expert
04/09/2018 - 02:09

This is the first part of the formula

Excelchat Expert
04/09/2018 - 02:09

=if(and(A2>=$G$3,A2<=$H$3),$I$3,

Excelchat Expert
04/09/2018 - 02:09

logical test 1 = A2>=G3

Excelchat Expert
04/09/2018 - 02:09

Logical test 2 = A2<=H3

User
04/09/2018 - 02:09

i get it

Excelchat Expert
04/09/2018 - 02:10

and if it is false the adding another IF CONDITION

Excelchat Expert
04/09/2018 - 02:10

so you will need to add more if condition to it. Presently it is for 3 conditions you have mentioned

User
04/09/2018 - 02:10

as the years change do i have to manually write in new cell values

User
04/09/2018 - 02:11

or will excel pick it up from the year

Excelchat Expert
04/09/2018 - 02:11

we have to add condition for each year

Excelchat Expert
04/09/2018 - 02:11

as triggers might change every year

User
04/09/2018 - 02:11

what if they dont change

Excelchat Expert
04/09/2018 - 02:12

if trigger remains constant through out the period, we can amend the formula to other version based on date and month

User
04/09/2018 - 02:12

the order repeats

Excelchat Expert
04/09/2018 - 02:12

at present it is accounting for date, month and year

Excelchat Expert
04/09/2018 - 02:12

if you want I can just make it for Date and month.

Excelchat Expert
04/09/2018 - 02:13

this will make it year free

User
04/09/2018 - 02:13

right

Excelchat Expert
04/09/2018 - 02:13

Should I make it year free?

User
04/09/2018 - 02:13

yes

Excelchat Expert
04/09/2018 - 02:13

Ok

Excelchat Expert
04/09/2018 - 02:13

Allow me a minute

Excelchat Expert
04/09/2018 - 02:18

=if(and(left(A2,2)>=left(G3,2),month(A2)=month(G3),left(A2,2)<=left(H3,2)),I3,"")

Excelchat Expert
04/09/2018 - 02:19

This is one of the If function I have created

Excelchat Expert
04/09/2018 - 02:19

Using Left(a2,2) - I am getting the left 2 values that is the date

User
04/09/2018 - 02:20

ok

Excelchat Expert
04/09/2018 - 02:20

and comparing it with the date of G3

User
04/09/2018 - 02:20

if i set up the triggers with jus a date and month would that do the trick

Excelchat Expert
04/09/2018 - 02:20

Yes

Excelchat Expert
04/09/2018 - 02:20

It would

User
04/09/2018 - 02:20

ok

Excelchat Expert
04/09/2018 - 02:21

Now let us work the Cumulative deviation formula

User
04/09/2018 - 02:21

yes

User
04/09/2018 - 02:23

may i add a condition here

Excelchat Expert
04/09/2018 - 02:23

Sure

User
04/09/2018 - 02:25

if the cumulative deviation is greater than 150 I get 150 dollars and (less than 250 ) if its greater than 250 I get 250 onwards upto a max of 15000 dollars

Excelchat Expert
04/09/2018 - 02:26

Ok. Let us first calculate the cumulative deviation and then we will add the condition

Excelchat Expert
04/09/2018 - 02:26

We just have 12 mins left

Excelchat Expert
04/09/2018 - 02:26

Let me try and cover everything

Excelchat Expert
04/09/2018 - 02:28

Check the cumulative for 31st Jan

User
04/09/2018 - 02:29

there is no formula showing up

User
04/09/2018 - 02:30

ok i see it

Excelchat Expert
04/09/2018 - 02:30

This is the same type If statement but since we need the value at the end of the period

Excelchat Expert
04/09/2018 - 02:31

So I am comparing the date and Month with the End Date of the trigger and then using the Sum function and offset to get the sum

User
04/09/2018 - 02:31

what does offset do

Excelchat Expert
04/09/2018 - 02:31

Let me explain How offset works

Excelchat Expert
04/09/2018 - 02:32

offset(C32,$J$3,0))

Excelchat Expert
04/09/2018 - 02:32

So what it will do it it will take C32 cell and then it will got up the value of J3 i.e. 30

Excelchat Expert
04/09/2018 - 02:32

j3 is the period

Excelchat Expert
04/09/2018 - 02:32

so if our period is 15

Excelchat Expert
04/09/2018 - 02:33

it will take up the cell reference 15 rows up

User
04/09/2018 - 02:33

i see

Excelchat Expert
04/09/2018 - 02:33

sum(C32:offset(C32,$J$3,0))

Excelchat Expert
04/09/2018 - 02:33

so what this will do is

Excelchat Expert
04/09/2018 - 02:33

Sum(C32:C2)

Excelchat Expert
04/09/2018 - 02:34

it will depend upon the number of days

User
04/09/2018 - 02:34

ok

Excelchat Expert
04/09/2018 - 02:34

So we need to set up Multiple If like this

Excelchat Expert
04/09/2018 - 02:34

to get the required data

Excelchat Expert
04/09/2018 - 02:34

We still have 3 mins

Excelchat Expert
04/09/2018 - 02:34

Let me address the last condition

Excelchat Expert
04/09/2018 - 02:35

if the cumulative deviation is greater than 150 I get 150 dollars and (less than 250 ) if its greater than 250 I get 250 onwards upto a max of 15000 dollars

User
04/09/2018 - 02:35

yes

User
04/09/2018 - 02:35

one dollar per cenlsius

Excelchat Expert
04/09/2018 - 02:35

Ok

Excelchat Expert
04/09/2018 - 02:35

what if it is less than 150?

User
04/09/2018 - 02:36

no money

Excelchat Expert
04/09/2018 - 02:36

Ok

Excelchat Expert
04/09/2018 - 02:37

=if(D32<150,"",If(and(D32>=150,D32<250),150,D32))

Excelchat Expert
04/09/2018 - 02:37

I have added 2 condition

Excelchat Expert
04/09/2018 - 02:37

you need to add 3rd condition similarly

Excelchat Expert
04/09/2018 - 02:37

for 2500

Excelchat Expert
04/09/2018 - 02:37

Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side and requesting you to provide good feedback and review so that I can continue my services to others.

Excelchat Expert
04/09/2018 - 02:38

We are almost out of time

Excelchat Expert
04/09/2018 - 02:38

I tried to cover all topics

Excelchat Expert
04/09/2018 - 02:38

Thank you

User
04/09/2018 - 02:38

thank you o much!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*