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.

Documents Reviewed	Document Tally	Pricing Tier 1 - Documents (1 - 250,000)	Pricing Tier 2 - Documents (250,001 - 500,000)	Pricing Tier 3 - Documents (500,001 - 1,000,000)	Pricing Tier 4 - Documents (1,000,001+)
138,466	138,466	must be 138,466 until B2 reaches 250000
142,765	281,231	-	281,231-142,765 if<500000 else move to T3 etc.
47,356	328,587	-
37,541	366,128	-
50,000	416,128	-
150,000	566,128	-
	566,128	-
	566,128	-
	566,128	-
	566,128	-
	566,128	-
		250000	500000

<colgroup><col span="6"></colgroup><tbody>
</tbody>

 

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

new number	Tally	Pricing Tier 1 - Documents (1 - 250,000)	Pricing Tier 2 - Documents (250,001 - 500,000)	Pricing Tier 3 - Documents (500,001 - 1,000,000)	Pricing Tier 4 - Documents (1,000,001+)
138,466	138,466	138,466	0	#REF!	#REF!
142,765	281,231	111,534	0	#REF!	#REF!
47,356	328,587	0	78,587	#REF!	#REF!
37,541	366,128	0	116,128	#REF!	#REF!
50,000	416,128	0	166,128	#REF!	#REF!
150,000	566,128	0		66,128	#REF!
100,000	666,128	0	0	#REF!	#REF!
70,000	736,128	0	0	#REF!	#REF!
60,000	796,128	0	0	#REF!	#REF!
3,000	799,128	0	0	#REF!	#REF!
295,000	1,094,128	0	0		#REF!
1,094,128		250,000	249,999	249,999	#REF!

<colgroup><col width="64" span="6" style="width: 48pt;"></colgroup><tbody>
</tbody>

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