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.
