LARGE With CountIf Function Giving Same Kth Value On Some Numbers.

Need one of you 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 perfectly 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)))

File

Thanks Ford. I gave a Dropbox file link at the bottom of my OP ("File"). Will that not suffice?

I believe it has to do with something about rounding for year 3 (cell e2). If you round that value to 2 digits, then all values seem correct. Or, if you multiply years 1 - 10 by 10, it all seems to work as well. I don't have the exact answer, but maybe this will point you (or someone) to something?

Originally Posted by JeteMc

You may get faster help if you upload directly to the site.
That said I can not say why the array formula gave incorrect values in F12:G12. What I can offer is an AGGREGATE based formula that seems to work correctly:

Formula:

Please Login or Register  to view this content.

Note that the formula should be pasted into D12 and dragged across.
Note that this is a regular formula and does not require CSE activation.
Let us know if you have any questions.

I'm really impressed with your formula, as I've never used 'Aggregate' function before. It works perfectly, but I'm confused as to how it's configured. You chose 15, which is for the Small functionality, which surpised me. I would have assumed the Large function would have been selected. Also, your PositionC/(PositionC>C12) gives me a calculation of .97322217. How does that factor into the overall calculation.

Also, please don't miscontrue this as a challenge to your formula. You obviously have excellent excel skills, which based on what you've provided, are better than mine. I just love the Aggregate formula you've shown me and would like use it more often going forward. Any help you can give would be appreciated. Thank you for your post

Originally Posted by JeteMc

Using the Evaluate Formula feature see that PositionC is an array of values and that PositionC>C12 will evaluate to an array of trues and falses. When true is the denominator it is equivalent to dividing by 1 and when false is the denominator it is equivalent to dividing by zero (hence the second argument is 6 which ignores the DIV/0 error). All the values that are larger than C12 will be available to the SMALL (15) function, and we only want the one that is next value after the one displayed in C12 (hence SMALL and 1 as the [K]). Hopefully I have explained well enough, let us know if not or if you have any questions.
If your query we has been resolved/addressed satisfactorily please mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Thank you for the explanation