Joining two formulas

[kunal kishore]

Hi this is Kishore.


I want to join two formulas.I have used the formulas seperately and it has given results but when i combine them it leads to mishap.My formulas are -


=IF(H6=1,"L1",if(h6=2,"L2",if(h6=3,"L3",if(h6=4,"L4",if(h6=5,"L5")))))


=and(RANK($G6,$G$6:$G$10,1),IF(H6=1,"L1",if(h6=2,"L2",if(h6=3,"L3",if(h6=4,"L4",

if(h6=5,"L5")))))

 

[Hui]

Kunal

When you say join, can you try and elaborate how you want the formulas joined?

 

[VaraK]

Hi Kunal,


Did you mean something like this?


H6 is the rank you are calculating for G6 from G6:G10? If yes, then the below formula works.


=IF(RANK($G6,$G$6:$G$10,1)=1,"L1",IF(RANK($G6,$G$6:$G$10,1)=2,"L2",IF(RANK($G6,$G$6:$G$10,1)=3,"L3",IF(RANK($G6,$G$6:$G$10,1)=4,"L4",IF(RANK($G6,$G$6:$G$10,1)=5,"L5")))))


You can also try:


="L"&RANK($G6,$G$6:$G$10,1). This will also give you the same result.

 

[kunal kishore]

Thanks Varak for the solution.


your solution is perfect but the problem is that i have no.of blank cells in between.I have to rank only those cells which have a value.

 

[kunal kishore]

Each cell here contains a value that comes after adding several rows in a range above it.Now here some cells in the range may be without a value i.e. blank.Thus the value that

comes after adding a range that contains a particular no.of blank cells should be ranked as invalid.

 

[VaraK]

Hi Kunal,


My previous formula ="L"&RANK($G6,$G$6:$G$10,1) only ranks the numbers (Infact, Rank formula ranks the numbers ). It throws up #N/A if the cell is blank. Let me know if this is not the case.


By using if and isna we can eliminate #N/A's and make that as invalid rank.

=IF(ISNA("L"&RANK($G6,$G$6:$G$10,1)),"Invalid Rank","L"&RANK($G6,$G$6:$G$10,1))


Did I get you right?

 

[kunal kishore]

Hi Varak


Your formula is perfect when we are ranking cell values in a row with few blank cells in between.But my question was whether we could make that cell value invalid which comes after adding a column where some cell values are missing.The cell value in this case would definitely be minimum.