# Excel - IF Function Problem - Expert Solution

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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.