• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Values from splitted range

F

fortune

Member
I have a range which consists of 1 row x 12 columns but columns are not continuous & are 'spitted' like B2:D2; G2:I2 & L2:Q2.
Each cell in the above range=value or "" (null).

Output required:
1. S2=Value of Rightmost cell (in the range) having value>0
2. T2=Value of Rightmost - 1 cell i.e. 1 cell LEFT to the above cell.
3. U2=MAXIMUM value in the range.
Y. V2=MINIMUM value in the range.

Lastly;
Cell address (RC1 style) of the above outputs in W2, X2, Y2, Z2 respectively.

Formula required in S2,T2,U2,V2,W2,X2,Y2 & Z2.
 
@fortune

There are more doubts:

1. If the last cell is say 5 and the left of it is a blank cell than what will be the result of 2 pt.

2. What is there are multiple max and min say 7 is max and it appear two times that what address in R1C1 stype should come
Similarly, for MIN also.

Regards,
 
Somendra Misra said:
@fortune

There are more doubts:

1. If the last cell is say 5 and the left of it is a blank cell than what will be the result of 2 pt.

2. What is there are multiple max and min say 7 is max and it appear two times that what address in R1C1 stype should come
Similarly, for MIN also.

Regards,
Click to expand...
If any cell has value the left cell of it will always contain a value (may be equal to 0 also) but NEVER "" (null) except the FIRST cell of the range because there is no cell to the left of FIRST cell.

For multiple max/min; the 'first' appearance has to be taken into account.
 
See the file.

Check for possible combinations of numbers, I had considered the range B2:Q2. Formula cell colour are given in pair, as per the combination.

Regards,
 

Attachments

  • fortune_chandoo_new.xlsx
    9.3 KB · Views: 5
fortune said:
If any cell has value the left cell of it will always contain a value (may be equal to 0 also) but NEVER "" (null) except the FIRST cell of the range because there is no cell to the left of FIRST cell.

For multiple max/min; the 'first' appearance has to be taken into account.
Click to expand...
Hi,


Not very elegant but this works on the non-contiguous range i.e. values in columns E, F, J & K are ignored.
 

Attachments

  • Chandoo.xlsx
    10.5 KB · Views: 4
Thanks for the feedback @fortune . I think you last two thread's on CF you have not yet reviewed them ;)

And I don't find @Mike H.. solution to be said as not elegant (my personal thought) ,as for the learner, it give you an dimension to think on so that you can apply the technique at a different problem.

Regards,
 
You must log in or register to reply here.
Back
Top