CODEWITHSUNDEEP
excel
dcpartners
dcpartners

Reputation: 5456

Handling in Excel

I got this calculated incremental column

10

12

14

20

25

I have a input value let say: 15

Now, because 15 is exceding 14 in this case the output value that I want is 14.

How do achieve this in Excel formula? Excel is not really my think so if you could guide me that will be good.

Thanks

Upvotes: 0

Views: 71

Answers (2)

Leon
Leon

Reputation: 1141

Try =SMALL($A$2:$A$6,COUNTIF($A$2:$A$6,"<="&C2)) where your values (10 12 14...) are stored in A2:A6 and C2 contains 15. works for me

Upvotes: 1

barrowc
barrowc

Reputation: 10679

Most of what you need can be done using a VLOOKUP formula. You haven't defined what should happen with an input value smaller than the first value in your list (e.g. an input value of 8). VLOOKUP will return the #N/A error value for that which may/may not be the behaviour you need.

Anyway, assuming that:

  • your list is in A1:A5
  • your list is in ascending order
  • your input value is in C1

the formula you need would be

=VLOOKUP(C1,A1:A5,1,TRUE)

The input value and list to search are obvious. The 1 specifies to return a value from the 1st column (obvious as you only have one column) and TRUE specifies that you want to return the largest value that is less than or equal to the input value (FALSE would specify an exact match only, TRUE is the default and could thus be omitted)

There's more info about VLOOKUP on the Microsoft Office site here

Upvotes: 0

Related Questions