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

Find lowest value based on conditions (Microsoft Professional Plus 2016)

P

Pasadu

Member
Sir, Please help me81002 find the lowest value. I do not use Office 365 version please. The value should not come from the USD columns or % Difference columns. Thank You.
 

Attachments

  • LO.xlsx
    10.7 KB · Views: 4
pecoflyer said:
This is the same technique as I described in https://chandoo.org/forum/threads/f...-conditions-i-dont-have-minif-function.48995/
Take that as an inspiration, add the other conditions, and you're good.
The purpose of a forum is NOT to do the work for you but help you understand and learn.
Click to expand...
You're right sir, I have tried it over and over but without getting it. I also tried Min(If(Mod(Column(E3:M3),2=0,E3:M3,"")) without getting the answer.
With the aggregate function,I tried adding AND and ISTEXT functions but still couldn't find a way out.
I still appreciate your response though.
Thank you.
 
Alternative solution using Power Query

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"KANS"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = "OMA" or [Attribute] = "SKYBLUE WAVES")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"KANS"}, {{"Min", each List.Min([Value]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"KANS"})
in
    #"Removed Columns"
 
Pasadu said:
Sir, I still have 0.02. All the same, thank you and i appreciate your time and effort.
View attachment 81010
Click to expand...

Hi,

1] Your formula: =AGGREGATE(15,6,E3:M3/(E2:M2<>"USD")/(E2:M2<>"% Price Difference"),1)

>> criteria of "Price Difference" is with 1 space in between

2] My formula: =AGGREGATE(15,6,E3:M3/(E2:M2<>"USD")/(E2:M2<>"% Price Difference"),1)

>> criteria of "Price Difference" is with 2 spaces in between

And,

Your source data E2:M2, "Price Difference" is with 2 spaces in between, please check!

81014
 
bosco_yip said:
Hi,

1] Your formula: =AGGREGATE(15,6,E3:M3/(E2:M2<>"USD")/(E2:M2<>"% Price Difference"),1)

>> criteria of "Price Difference" is with 1 space in between

2] My formula: =AGGREGATE(15,6,E3:M3/(E2:M2<>"USD")/(E2:M2<>"% Price Difference"),1)

>> criteria of "Price Difference" is with 2 spaces in between

And,

Your source data E2:M2, "Price Difference" is with 2 spaces in between, please check!

View attachment 81014
Click to expand...
I am sorry for the oversight. I appreciate your time and correction. I checked it, and corrected it, and now it works perfectly. Thank you so much sir.
 
AlanSidman said:
and did you evaluate the solution I provided or did you ignore it. A comment would be appreciated as we do spend time here trying to help YOU>
Click to expand...
Let me attend to it please. This escaped me after the answer I received before your comment, solved the problem.
Will get back to you asap
 
AlanSidman said:
Alternative solution using Power Query

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"KANS"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = "OMA" or [Attribute] = "SKYBLUE WAVES")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"KANS"}, {{"Min", each List.Min([Value]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"KANS"})
in
    #"Removed Columns"
Click to expand...
Im sorry for the late reply sir, i really apologize for it.
I have tested it, but i am confused as to how to generate the answer.
Will the answer show in a cell or I will have to go to Power query to locate the answer?
 
You must log in or register to reply here.
Back
Top