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

[Pasadu]

Sir, Please help me 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.

 

[pecoflyer]

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.

 

[Pasadu]

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.

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.

 

[bosco_yip]

Do you have MINIFS function in your Excel "Microsoft Professional Plus 2016" version?

Or,

Try the following formula:



Regards

 

[Pasadu]

Do you have MINIFS function in your Excel "Microsoft Professional Plus 2016" version?

Or,

Try the following formula:

View attachment 81005

Regards

Thank you sir but I am getting a different results. I want to use the aggregate formula though. Thank You. No i don't have MINIFS function please.

 

[bosco_yip]

Thank you sir but I am getting a different results. I want to use the aggregate formula though. Thank You. No i don't have MINIFS function please.

View attachment 81006

But my computer returns the expected result in cell H8?

Please check your cell H8 "Cell format," and ensure it is in: "General" setting

 

[Pasadu]

But my computer returns the expected result in cell H8?

Please check your cell H8 "Cell format," and ensure it is in: "General" setting

View attachment 81008

Sir, I still have 0.02. All the same, thank you and i appreciate your time and effort.

 

[AlanSidman]

Alternative solution using Power Query

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"

 

[bosco_yip]

Sir, I still have 0.02. All the same, thank you and i appreciate your time and effort.
View attachment 81010

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!

 

[Pasadu]

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

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]

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>

 

[Pasadu]

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>

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

 

[Pasadu]

Alternative solution using Power Query

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"

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?