Large/Countif Not Giving Me The Correct K

[DR RENO]

Need excel gurus to help spot what's going on here. I have a named range that has a value for each of 10 periods. I'm using a Large/Countif formula to identify from other IF statement criteria, which period in named range would be captured for the table I have the formula in. My forumla works for just about every value I put in the reference cell (G5), but for some reason any number below 28,200 gives me the same number for year 3, year 4, and year 5. Any value above 28200, gives the correct increment in accordance to the named range. I have nothing in my formula that should put constraints in to do this. I'm not seeing what could be causing this problem. Any help you can give would be much appreciated.
=IF(C12>=$L$2,$L$2,LARGE(PositionC,COUNTIF(PositionC,">"&C12)))
Here's The File

 

[RoryA]

I think it's a floating point issue. Round the formulas in the pay increase cells to 3 decimal places and it should sort it.

 

[theBardd]

I think your problem is that there is precision greater than 3 in your numbers. If you round the salaries in PositionC to 2 dec pl, or even 3, it should work.

 

[theBardd]

I think it's a floating point issue. Round the formulas in the pay increase cells to 3 decimal places and it should sort it.

Touché Mr A

 

[RoryA]

En garde Mr P.

 

[AlanY]

i've put 35000 in G5 with results below, are those the numbers expected?

Book1
	A	B	C	D	E	F	G
1	Position C - Minimum Starting Salary (Business Case Proposal)	$26,285	Year1	Year2	Year3	Year4	Year5
2	Annual Pay Increase	3.50%	$26,285.000	$27,204.975	$28,157.149	$29,142.649	$30,162.642
3						Standard
4
5	Start Date	1-Feb-15	2015	Actual Starting Salary	$30,500	Current Salary	$ 35,000.00
6	Current Date	16-May-19	2016	3.50%
7	Years With Company	4	2017	3.50%
8			2018	3.50%
9			2019	3.50%
10
11	Scenarios	5-Year Cost	Year1	Year2	Year3	Year 4	Year5
12	1. Using The Salary Range In B1:L1	$178,295	$35,000.000	$35,823.757	$35,823.757	$35,823.757	$35,823.757
13	2. Using Starting Salary as Current Salary w/ Pay increase 3.5% per year until Max Salary (As Discussed morning of 2-May-19)	$178,295	$35,000	$35,824	$35,824	$35,824	$35,824
Salary Analysis Example

Cell Formulas
Range	Formula
D12	{=IF(C12>=$L$2,$L$2,LARGE(PositionC,COUNTIF(PositionC,">"&C12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DR RENO]

i've put 35000 in G5 with results below, are those the numbers expected?

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Position C - Minimum Starting Salary (Business Case Proposal)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]$26,285[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year5[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Annual Pay Increase[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]3.50%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]$26,285.000 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]$27,204.975 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]$28,157.149 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]$29,142.649 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]$30,162.642 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Standard[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Start Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1-Feb-15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] , align: center"]2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "]Actual Starting Salary[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]$30,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Current Salary[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]$ 35,000.00 [/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Current Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]16-May-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]3.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]Years With Company[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]3.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]3.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]3.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Scenarios[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]5-Year Cost[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Year1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Year2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Year3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Year 4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Year5[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFC000"]1. Using The Salary Range In B1:L1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]$178,295 [/TD]
[TD="align: center"]$35,000.000 [/TD]
[TD="align: center"]$35,823.757 [/TD]
[TD="align: center"]$35,823.757 [/TD]
[TD="align: center"]$35,823.757 [/TD]
[TD="align: center"]$35,823.757 [/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFC000"]2. Using Starting Salary as Current Salary w/ Pay increase 3.5% per year until Max Salary (As Discussed morning of 2-May-19)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]$178,295 [/TD]
[TD="align: center"]$35,000 [/TD]
[TD="align: center"]$35,824 [/TD]
[TD="align: center"]$35,824 [/TD]
[TD="align: center"]$35,824 [/TD]
[TD="align: center"]$35,824 [/TD]

</tbody>

Salary Analysis Example​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]{=IF(C12>=$L$2,$L$2,LARGE(PositionC,COUNTIF(PositionC,">"&C12)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

It does, but when you change G5 to 22,000 it does not work. Making it an array does not fix the problem.

 

[AlanY]

ok, would you let us know with 22000 in G5, which cells give wrong answer and what are they supposed to be?

Book1
	A	B	C	D	E	F	G
1	Position C - Minimum Starting Salary (Business Case Proposal)	$26,285	Year1	Year2	Year3	Year4	Year5
2	Annual Pay Increase	3.50%	$26,285.000	$27,204.975	$28,157.149	$29,142.649	$30,162.642
3						Standard
4
5	Start Date	1-Feb-15	2015	Actual Starting Salary	$19,172	Current Salary	$ 22,000.00
6	Current Date	16-May-19	2016	3.50%
7	Years With Company	4	2017	3.50%
8			2018	3.50%
9			2019	3.50%
10
11	Scenarios	5-Year Cost	Year1	Year2	Year3	Year 4	Year5
12	1. Using The Salary Range In B1:L1	$137,961	$26,285.000	$27,204.975	$28,157.149	$28,157.149	$28,157.149
13	2. Using Starting Salary as Current Salary w/ Pay increase 3.5% per year until Max Salary (As Discussed morning of 2-May-19)	$140,952	$26,285	$27,205	$28,157	$29,143	$30,163
Salary Analysis Example

 

[AlanY]

from what I can see, with 35,000 Rows 12 & 13, Years 2 to 5 are identical,

as with 22,000, Row 12 Years 3 to 5 are identical

 

[RoryA]

May I recommend posts #2 and #3 ?