Nested IF function

[StopItPoppet]

Hi
Im quite new to this site, loving it already. This question might have been asked numerous times already, just dont know what to search for. I require assistance in creating tiers. This is based on tally system. Apologies if it is not clear enough. Not sure how to ask this correctly.
[TABLE="class: grid, width: 744"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]Documents Reviewed[/TD]
[TD]Document Tally[/TD]
[TD]Pricing Tier 1 - Documents
(1 - 250,000)[/TD]
[TD]Pricing Tier 2 - Documents
(250,001 - 500,000)[/TD]
[TD]Pricing Tier 3 - Documents
(500,001 - 1,000,000)[/TD]
[TD]Pricing Tier 4 - Documents
(1,000,001+)[/TD]
[/TR]
[TR]
[TD]138,466[/TD]
[TD]138,466[/TD]
[TD]must be 138,466 until B2 reaches 250000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]142,765[/TD]
[TD]281,231[/TD]
[TD]-[/TD]
[TD] 281,231-142,765 if<500000 else move to T3 etc.[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]47,356[/TD]
[TD]328,587[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]37,541[/TD]
[TD]366,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50,000[/TD]
[TD]416,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]150,000[/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]566,128[/TD]
[TD] -[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]250000[/TD]
[TD]500000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

not sure exactly what you are doing
can you explain in words

as you are using cell ref , can you use the headings - i dont know for sure what cell b2 is

 

[StopItPoppet]

crappy at getting my q across... lets try again. When the total of column 3(PT1) reaches 250000, column 4 (PT2) should be populated until the total in column 4 reaches 250000 giving the overall total 500000. when this is reached, column 5(PT5) should be populated until that reaches 250000 as well.
[TABLE="class: grid, width: 384"]
<colgroup><col width="64" span="6" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"]new number[/TD]
[TD="width: 64"]Tally[/TD]
[TD="width: 64"]Pricing Tier 1 - Documents
(1 - 250,000)[/TD]
[TD="width: 64"]Pricing Tier 2 - Documents
(250,001 - 500,000)[/TD]
[TD="width: 64"]Pricing Tier 3 - Documents
(500,001 - 1,000,000)[/TD]
[TD="width: 64"]Pricing Tier 4 - Documents
(1,000,001+)[/TD]
[/TR]
[TR]
[TD="width: 64"]138,466[/TD]
[TD="width: 64"]138,466[/TD]
[TD="width: 64"]138,466[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]142,765[/TD]
[TD="width: 64"]281,231[/TD]
[TD="width: 64"]111,534[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]47,356[/TD]
[TD="width: 64"]328,587[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]78,587[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]37,541[/TD]
[TD="width: 64"]366,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]116,128[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]50,000[/TD]
[TD="width: 64"]416,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]166,128[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]150,000[/TD]
[TD="width: 64"]566,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]66,128[/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]100,000[/TD]
[TD="width: 64"]666,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]70,000[/TD]
[TD="width: 64"]736,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]60,000[/TD]
[TD="width: 64"]796,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]3,000[/TD]
[TD="width: 64"]799,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]#REF![/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]295,000[/TD]
[TD="width: 64"]1,094,128[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"]0[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]#REF![/TD]
[/TR]
[TR]
[TD="width: 64"]1,094,128[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]250,000[/TD]
[TD="width: 64"]249,999[/TD]
[TD="width: 64"]249,999[/TD]
[TD="width: 64"]#REF!

[/TD]
[/TR]
</tbody>[/TABLE]

hopefully this helps

 

[etaf]

Excel Workbook
	A	B	C	D	E	F
1	new number	Tally	Pricing Tier 1 - Documents	Pricing Tier 2 - Documents	Pricing Tier 3 - Documents	Pricing Tier 4 - Documents
2			(1 - 250,000)	(250,001 - 500,000)	(500,001 - 1,000,000)	(1,000,001+)
3	138,466	138,466	138,466	0	0	0
4	142,765	281,231	0	281231	0	0
5	47,356	328,587	0	328587	0	0
6	37,541	366,128	0	366128	0	0
7	50,000	416,128	0	416128	0	0
8	150,000	566,128	0	0	566128	0
9	100,000	666,128	0	0	666128	0
10	70,000	736,128	0	0	736128	0
11	60,000	796,128	0	0	796128	0
12	3,000	799,128	0	0	799128	0
13	295,000	1,094,128	0	0	0	1094128
14	1,094,128	2,188,256	0	0	0	2188256
Sheet1

 

[etaf]

The previous post

formula is putting the accumulated total in each cell Column B
if you want to put in column A numbers

then change the formula so that A3 is in the TRUE or false part

see next post

 

[etaf]

Excel Workbook
	A	B	C	D	E	F
1	new number	Tally	Pricing Tier 1 - Documents	Pricing Tier 2 - Documents	Pricing Tier 3 - Documents	Pricing Tier 4 - Documents
2			(1 - 250,000)	(250,001 - 500,000)	(500,001 - 1,000,000)	(1,000,001+)
3	138,466	138,466	138,466	0	0	0
4	142,765	281,231	0	142765	0	0
5	47,356	328,587	0	47356	0	0
6	37,541	366,128	0	37541	0	0
7	50,000	416,128	0	50000	0	0
8	150,000	566,128	0	0	150000	0
9	100,000	666,128	0	0	100000	0
10	70,000	736,128	0	0	70000	0
11	60,000	796,128	0	0	60000	0
12	3,000	799,128	0	0	3000	0
13	295,000	1,094,128	0	0	0	295000
14	1,094,128	2,188,256	0	0	0	1094128
Sheet1

 

[StopItPoppet]

Thanks for this. However, the columns need to add up 250000/249999 before the next column can be populated. So eg. i have my first 200k which falls in Tier1. My next entry is 100k, this amount is split 50k should be for tier 1 giving me a total of 250k in the column/tier and the difference 50k is placed in tier 2

 

[FormR]

Hi, your expected results in post#3 are a little odd, you seem to go into a cumulative sum when you get Tier 2.

Here is how I would do it, formulas in the top row are copied down.

Excel 2013/2016
	A	B	C	D	E	F
2	new number	Tally	(1 - 250,000)	(250,001 - 500,000)	(500,001 - 1,000,000)	(1,000,001+)
3	138,466	138,466	138,466	-	-	-
4	142,765	281,231	111,534	31,231	-	-
5	47,356	328,587	-	47,356	-	-
6	37,541	366,128	-	37,541	-	-
7	50,000	416,128	-	50,000	-	-
8	150,000	566,128	-	83,872	66,128	-
9	100,000	666,128	-	-	100,000	-
10	70,000	736,128	-	-	70,000	-
11	60,000	796,128	-	-	60,000	-
12	3,000	799,128	-	-	3,000	-
13	295,000	1,094,128	-	-	200,872	94,128
14	1,094,128		250,000	250,000	500,000	94,128
Sheet1

Cell Formulas
Range	Formula
C3	=MAX(0,SUM($A3:$A3)+MIN(0,250000-SUM($A$3:$A3)))
D3	=MAX(0,$A3+MIN(0,500000-SUM($A$3:$A3)))-SUM($C3:C3)
E3	=MAX(0,$A3+MIN(0,1000000-SUM($A$3:$A3)))-SUM($C3:D3)
F3	=MAX(0,$A3+MIN(0,9.99E+307-SUM($A$3:$A3)))-SUM($C3:E3)

 

[StopItPoppet]

Hi, this is exactly what im looking for!!!!