Get the next possible larger value next to the current value

  • Hi,


    I have a data where I want to create a formula fulfilling the below conditions.


    Suppose I have two fields: Table1(A) and Table2(B)
    If a value ex.98 (A COLUMN) matches with the value with 98(B COLUMN),it should pick up my second higher value 103. suppose 103 is the next high value of 98 .


    [TABLE="width: 903"]

    [tr]


    [td]

    TABLE1

    [/td]


    [td]

    TABLE2

    [/td]


    [td][/td]


    [td]

    Required Result

    [/td]


    [td]

    Condition

    [/td]


    [td]

    OUTPUT

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]98[/TD]
    [TD="align: right"]98[/TD]

    [td][/td]


    [td]

    98 then 103

    [/td]


    [td]

    if value of table1=98 then 103from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]103[/TD]
    [TD="align: right"]103[/TD]

    [td][/td]


    [td]

    103 then 149

    [/td]


    [td]

    if value of table1=103 then 149from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]149[/TD]
    [TD="align: right"]149[/TD]

    [td][/td]


    [td]

    149 then 175

    [/td]


    [td]

    if value of table1=149 then 175from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]175[/TD]
    [TD="align: right"]175[/TD]

    [td][/td]


    [td]

    175 then 198

    [/td]


    [td]

    if value of table1=175 then 198from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]198[/TD]
    [TD="align: right"]198[/TD]

    [td][/td]


    [td]

    198 then 199

    [/td]


    [td]

    if value of table1=198 then 199from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]199[/TD]
    [TD="align: right"]199[/TD]

    [td][/td]


    [td]

    199 then 201

    [/td]


    [td]

    if value of table1=199 then 201from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]201[/TD]
    [TD="align: right"]201[/TD]

    [td][/td]


    [td]

    201 then 245

    [/td]


    [td]

    if value of table1=201 then 245from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]245[/TD]
    [TD="align: right"]245[/TD]

    [td][/td]


    [td]

    245 then 251

    [/td]


    [td]

    if value of table1=245 then 251from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]251[/TD]
    [TD="align: right"]251[/TD]

    [td][/td]


    [td]

    251 then 351

    [/td]


    [td]

    if value of table1=251 then 351from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]351[/TD]
    [TD="align: right"]351[/TD]

    [td][/td]


    [td]

    351 then 352

    [/td]


    [td]

    if value of table1=351 then 352from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]352[/TD]
    [TD="align: right"]352[/TD]

    [td][/td]


    [td]

    352 then 451

    [/td]


    [td]

    if value of table1=352 then 451from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]451[/TD]
    [TD="align: right"]451[/TD]

    [td][/td]


    [td]

    451 then 499

    [/td]


    [td]

    if value of table1=451 then 499from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]499[/TD]
    [TD="align: right"]499[/TD]

    [td][/td]


    [td]

    499 then 598

    [/td]


    [td]

    if value of table1=499 then 598from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]598[/TD]
    [TD="align: right"]598[/TD]

    [td][/td]


    [td]

    598 then 599

    [/td]


    [td]

    if value of table1=598 then 599from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]599[/TD]
    [TD="align: right"]599[/TD]

    [td][/td]


    [td]

    599 then 601

    [/td]


    [td]

    if value of table1=599 then 601from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]601[/TD]
    [TD="align: right"]601[/TD]

    [td][/td]


    [td]

    601 then 751

    [/td]


    [td]

    if value of table1=601 then 751from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]751[/TD]
    [TD="align: right"]751[/TD]

    [td][/td]


    [td]

    751 then 851

    [/td]


    [td]

    if value of table1=751 then 851from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]851[/TD]
    [TD="align: right"]851[/TD]

    [td][/td]


    [td]

    851 then 949

    [/td]


    [td]

    if value of table1=851 then 949from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]949[/TD]
    [TD="align: right"]949[/TD]

    [td][/td]


    [td]

    949 then 951

    [/td]


    [td]

    if value of table1=949 then 951from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]951[/TD]
    [TD="align: right"]951[/TD]

    [td][/td]


    [td]

    951 then 1251

    [/td]


    [td]

    if value of table1=951 then 1251from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]1251[/TD]
    [TD="align: right"]1251[/TD]

    [td][/td]


    [td]

    1251 then 1501

    [/td]


    [td]

    if value of table1=1251 then 1501from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]1501[/TD]
    [TD="align: right"]1501[/TD]

    [td][/td]


    [td]

    1501 then 1501

    [/td]


    [td]

    if value of table1=1501 then 1501from table 2 (next large number)

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Get the next possible larger value next to the current value


    Are your real data like your sample...?


    In this case just use an if function in D2 and copy down.


    =IF(A2=B2,B3,B2)

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Get the next possible larger value next to the current value


    =IF(A2=B2,SMALL($B$2:$B$23,1+COUNTIF($B$2:$B$23,"<="&B2)),B2)


    @ StephenR


    Actually he did it! :yourock:

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Get the next possible larger value next to the current value


    In your first post, you asked for this.


    Quote

    ...If a value ex.98 (A COLUMN) matches with the value with 98(B COLUMN),it should pick up my second higher value 103


    Formula does this.


    IF A2=B2, then will give you the next higher value of B2. IF a2<>b2, then will gives B2 value.


    In your last example no cell of column B matces with cell in column A. So the result is the value of column B.


    Without any condition this formula in C2 and copy down, gives your expected results.


    =SMALL($B$2:$B$23,ROW(B2))



    Edit: Or..


    =IF(MATCH(A2,$B$2:$B$23,0),SMALL($A$2:$A$23,1+COUNTIF($A$2:$A$23,"<="&A2)),A2)

    Files

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Get the next possible larger value next to the current value


    misprepaid.asm: please don't treat people answering questions here as if they were your lackeys. And take the time to set out all the details of your question at the beginning - this avoids all concerned wasting time.

  • Re: Get the next possible larger value next to the current value


    Re: post #4


    Just so you are clear about bumping threads:


    Allow a minimum of 18-24 hours to elapse before you bump your thread. This is a free forum staffed by volunteers generously giving of their time and talent.
    Give adequate time for someone to take interest.

  • Re: Get the next possible larger value next to the current value


    Quote from StephenR;650189

    misprepaid.asm: please don't treat people answering questions here as if they were your lackeys. And take the time to set out all the details of your question at the beginning - this avoids all concerned wasting time.




    I apologize for my mistake..I will surely keep in mind from this thime onwards.

  • Re: Get the next possible larger value next to the current value


    Yes. keep in mind for the next time.... This time I was obliged (as a good servant) to do .. No need to thank me for it. it was just .. my job.:0ops:


    From simple curiosity, what was ultimately the solution you wanted?

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!