• 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.

Finding Difference within a Category

V

Venkat76

New Member
Dear Friends - am trying to find a difference in values within a category i.e., difference of a value in a row from that of the Minimum - all within that category. For eg.

category value Diff
1 100 0
1 120 20
1 130 30
2 50 0
2 60 10

I have provided manually the Difference column which is the desired one - please help with a formula.

Thanks.
 
in C2, use Array Formula as.
=B2-MIN(IF($A$2:$A$6=A2,$B$2:$B$6))

Please confirm the formula by pressing Ctrl + Shift + Enter, not just Enter
 
Hi - as an extension, I would like to arrive at a 'desired preference order'; now that I have calculated the difference basis above, I have attached an example where I would like to check the least cost alternative basis cost rank for a difference less than 6 in transit. In other words, check less than 6 first and rank basis cost, then more than 6 and rank basis cost from thereon. And order preference accordingly (colored yellow in attached file).

Please help. Thanks.
 

Attachments

Hi Somendra - the "Diff" in column C is the difference in transit time from the minimum within the category. So while I want to choose the low cost option within a category, I also want to check that the low cost option has a transit difference less than 6 days (this is because faster transit has higher cost, so 6 days difference is like a cutoff). The attached file earlier has the working..
Look forward.
 
Dear Narayan, Yes, correct. Thanks...as my database real costs can be in the range of 5 to 5000, please advise significance of helper column to be plus 10,000..? Would the above cost range impact the helper column differently?
 
Hi Venkat ,

Basically within each category in column A , we have two sub-categories ; one where the difference is less than 6 and the other where the difference is greater than 6.

If the maximum amounts are less than 5000 , then , an amount of 5000 ( maximum amount ) in one sub-category needs to be differentiated from another amount of say 500 ( minimum amount ) in the other sub-category. The helper column adds 10000 to get this differentiation.

If the maximum amount will go up to 10000 , then you will need to increase this multiplier to probably 100000.

Narayan
 
Tri using below array formula. Enter with Ctrl+Shift+Enter in G2 and copy down:

=IF(C3<=6,SUMPRODUCT(($A$3:$A$8=A3)*($C$3:$C$8<=6)*(E3>=$E$3:$E$8)),MAX(IF($A2:$A$3=A3,$F2:$F$3))+SUMPRODUCT(($A$3:$A$8=A3)*($C$3:$C$8>6)*($D$3:$D$8=MIN(IF(A3=$A$3:$A$8,$D$3:$D$8)))))

Regards,
 
Hi Somendra - thanks, but the formula includes F column which is my manual input as a desired rank hence should not be part of calculation...please advise. Also what if one cell is blank, say, in Col.D Cost. How to exclude this row?
 
Thanks Narayan for the clarification, will keep this in mind. How about if one cell is blank, say in Col.D cost? Is it possible exclude this from the formula or helper? Am working with a large dataset of about 11,000 rows...
 
@Venkat76

As an OP, when you post some question, you must post all the possiblities of your problem all together and make your sample file in such a way that it should take atleast one all such conditions. It saves a lot of your and other users time.

Are there are chances of any other columns remaining blanks other than D, and if any such column is blank for a row what total result are you expecting?

Regards
 
Hi - fair point. Agree..as am new to the forum, getting used to querying method. Also my database keeps giving me surprises. Will recheck and confirm.
 
Hi - yes blank only in Col D cost value. If a cost value is blank or "-", then would like to exclude this row itself from calculation and place preferences for the remaining within the category. Have highlighted the row in green; desired rank in yellow as attached..Thanks.
 

Attachments

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