How to use nested IF statement with a running total?

[Waimea]

I am trying to use a nested IF statement but I can't get it to work.

[TABLE="width: 530"]
<tbody>[TR]
[TD]Year[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD][B]Running total[/B][/TD]
[TD][/TD]
[TD="align: right"][B]0[/B][/TD]
[TD="align: right"][B]0[/B][/TD]
[TD="align: right"][B]2[/B][/TD]
[TD="align: right"][B]3[/B][/TD]
[TD="align: right"][B]4[/B][/TD]
[/TR]
</tbody>[/TABLE]

In cell H34 (running total for 2013 = 0):

=IF(G34<>0;0;
  IF(G33+H33>50;1;
  IF(H33+G33>70;2;
  IF(H33+G33>150;3;
  IF(H33+G33>210;4;0)))))

If the running total is 1,2,3 or 4 I want it to ignore the previous amount and then start over with a new count of amount.

[TABLE="width: 530"]
<tbody>[TR]
[TD]Year[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD][B]Running total[/B][/TD]
[TD][/TD]
[TD="align: right"][B]0[/B][/TD]
[TD="align: right"][B]0[/B][/TD]
[TD="align: right"][B]1[/B][/TD]
[TD="align: right"][B]0[/B][/TD]
[TD="align: right"][B]2[/B][/TD]
[/TR]
</tbody>[/TABLE]

Ex.

If the amount of 2013 and 2014 is below 50 I want to have a 0 in running total.

If the amount of 2013 and 2014 is higher then 50-100 I want to have a 1 in running total.

if the amount of 2013 and 2014 is higher then 101-140 I want to have a 2 in running total.

If the amount of 2013 and 2014 is higher then 141 to 210 I want to have a 3 in running total. Anything above will be a 4.

But if the previous running total was 1,2,3 or 4 I want to reset the count. So if running total for 2015 was 3, I want the count for 2016 to ignore the previous amount. So that the running total doesn't say 2,3,4 etc.

I am not sure how to make it work?

 

[jasonb75]

Is this what you want?

=LOOKUP(SUM($G33:H33);{0;50;70;150;210};{0;1;2;3;4})

Your examples and explanations appear to contradict each other in places.

 

[pella88]

Hi Waimea,

I would suggest creating a helping table with three columns: lower_bound, upper_bound and running_total. This is of course not needed, but it would reduce number of nested IF's.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lower_Bound[/TD]
[TD]Upper_Bound[/TD]
[TD]Running_Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]101[/TD]
[TD]140[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]141[/TD]
[TD]210[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]211[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Then in your main table where calculation is done, you would have following:
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Year[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Amount[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]34[/TD]
[TD]54[/TD]
[TD]67[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

In C12 you would put formula:
=IF(B12>0,0,VLOOKUP(C11,$A$1:$C$6,3,TRUE))

and just drag it to the right.

Granted, this is if I understood correctly how running total is established (based on each individual Amount). If there is calculation for the the value which is tested according to the lower and upper bound, please let me know, and will adjust the formula accordingly.

Br
pella88

 

[Waimea]

Hi jasonb75 and pella88,

thank you for your replies!

I will try your approach pella88, I think it's what I am trying to do.

I will be back in a while when I have tried it out!

 

[jasonb75]

Pella's suggestion gives the same results as your original formula so I'm lost as to what you're trying to achieve.

 

[Waimea]

Hi jasonb75,

thank you for your reply. I apoligize if my logic is flawed, I am not sure of what I want to do.

I think that I want to count the amount of two consectutive years and then lookup the value of the combined score for the two consetutive years.

But if the consecutive score is "used" in one year I don't want to count it again! So if the amount of 2015 and 2016 is above 100 it would give a running total of 2, 2016 and 2017 would be a 3 in my orginal formula but possible a 1 in the running total?

 

[jasonb75]

Not quite the same results as you shown in post #1 , but perhaps in H34

=LOOKUP(IF(G34>0;H33;SUM(G33:H33));{0;50;70;150;210};{0;1;2;3;4})

or

=IF(G34>0;0;LOOKUP(SUM(G33:H33);{0;50;70;150;210};{0;1;2;3;4}))

 

[Waimea]

Hi jasonb75,

thank you for your reply. I guess that in principle what I am trying to do is the divivide the amount of two consecutive years in multiples of 60 and rounding up towards whole numbers.

Ex.

The amount is 64 in year 2015 and amount is 30 in year 2016.

The sum would be 94 and the running total would be 1?

The amount is 30 in year 2016 and and 84 in year 2017, the sum would be 114 and the running total would be 2?

But I don't want to count the amount of 30 for 2016 in the 2017 year calculation since I have already assigned those amounts towards the running total.

 

[Waimea]

I have tried different nested IF, IFS, IF AND and IF and OR and I can't get this to work.

Any more suggestions on how I can proceeed?

 

[jasonb75]

Last attempt, H33 then drag right

=MIN(IF(G34=0,ROUND((G33+H33)/60,0),0),4)