How to use nested IF statement with a running total?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
I am trying to use a nested IF statement but I can't get it to work.

Code:
[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):

Code:
=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.

Code:
[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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

Board Regular
Joined
Aug 14, 2013
Messages
67
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.

ABC
1Lower_BoundUpper_BoundRunning_Total
20500
3511001
41011402
51412103
62114

<tbody>
</tbody>

Then in your main table where calculation is done, you would have following:
ABCDEFG
10Year201220132014201520162017
11Amount01534546788
1200101

<tbody>
</tbody>

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

Active Member
Joined
Jun 30, 2018
Messages
342
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Pella's suggestion gives the same results as your original formula so I'm lost as to what you're trying to achieve.
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

Active Member
Joined
Jun 30, 2018
Messages
342
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.
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Last attempt, H33 then drag right

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

Forum statistics

Threads
1,078,215
Messages
5,338,905
Members
399,267
Latest member
Danielle1017

Some videos you may like

This Week's Hot Topics

Top