Need Formula for price comparison

[raeesasif2022]

Hello Sirs,

I need formula for least 3 quotes (excluding zero) according to min. rate, vendor name and availability for price comparison.

please review the attached file.


BR, RAEES

[Gregb11]

I don't understand what the results should be. Can you fill in the EXPECTED Results and re-attach the sample?

[raeesasif2022]

my focus is for min. rate first............if it is with availability as "Ready Stock" . so i need this with vendor name...........otherwise go to second option with 2nd min rate and availability so on, like this for 3rd option too. i will also share file with expected results too.

[Gregb11]

So first you look to see all the ones that have "Availability" = "Ready Stock" and of those, pick the one with the minimum rate first, then the next minimum rate, etc. If there are less than 3 of those, then you look at the ones that do not have an "Availability" of "Ready Stock" and of those, pick the one with the minimum rate first. Do you split these out such that those with an Availability of "Not quoted" go last? Would you group all the others together and just sort those by minimum rate, or do you look at the time of Availability and base it off which ones are available sooner? Of course, it would be a little difficult to say, "look at the ones that say "2~3 days" and pick that over one that says "3~4 Days" regardless of minimum rate, right?

[raeesasif2022]

Actually, I want to make formula as simple as possible, not more complex.
for that, my first choice is to get the least minimum rate from out of five vendors and with this check the availability status of material, which can be anything like ...not in stock, 1~2 days or ready stock etc. with their vandor name... if 1st min. rate has no availability or delay etc. i will move to 2nd min. rate with the availability status and so on with 3rd one. ...the expected result may be like this..

1st Option
250 not in stock vendor 1

2nd Option
300 2~3 days vendor 2

3rd Option
350 ready stock vendor 3

in that case i have option 3 for ordering. the result can be vary as per availability of "Ready Stock" of material. other reasons shall assume non-availability.

hope you understand.


BR, RAEES

[windknife]

Try these,

Y10

Please Login or Register  to view this content.

Z10

Please Login or Register  to view this content.

AA10

Please Login or Register  to view this content.

all copied down.

copy Y10:AA11 to AB10:AD11 and AE10:AG11

[raeesasif2022]

Thank you Sir, I tried this for price comparison it is working fine...........however, i want to make "Min Rate" columns formula based, like before this column i have required. two more columns one for price and the other one for disc.% ...... so the value will come after calculation in "Min Rate" column..... for now i enter directly a discounted rate in this column .... which i manually calculate on other sheet.... than put in this column.

A B C
Example: Actual Rate Disc% Min. Rate
500 5% =A2*(1-B2) value will be 475 and this value will come in my min rate options array ?

[windknife]

I don't sure what you want. Upload a sample file with your expected results.

[raeesasif2022]

Sample file attached. please check....now the MIN. RATE column has formula and i am entering the data in other column (incl. gst rate) in red font.
expected result also mentioned.

[windknife]

Ba10

Please Login or Register  to view this content.

bb10

Please Login or Register  to view this content.

bc10

Please Login or Register  to view this content.

[Gregb11]

I'm going to assume the sample file you attached in POST #9 was not exactly correct and that you want the 3 options to be in the exact same order:
MIN RATE VENDOR AVAILABILITY

Please try in cell BA10:
=TOROW(TAKE(SORT(TRANSPOSE(VSTACK(FILTER(M10:AY10,M9:AY9="MIN RATE"),FILTER(M8:AY8,M8:AY8<>""),FILTER(M10:AY10,M9:AY9="AVAILABILITY"))),1,1),3))

This one formula fills in all the results.

(I'm hoping your version has TAKE and TOROW as formulas. If not, you would need MS365 version

[raeesasif2022]

@windknife

the attached file is same , you shared with me earlier......i have attached book2 with my new problem please check this ..

[raeesasif2022]

@Gregb11

haven't MS 365...Please add formula which is compatible with MS 2016

[AliGW]

Wait - what? Your forum profile says Excel 2021 - why does it need to be for Excel 2016?

Please update your profile NOW.

@Gregb11

TOROW and VSTACK aren't in Excel 2021.

[windknife]

I chose wrong file. see this attachment.

[raeesasif2022]

profile updated

[raeesasif2022]

Also please confirm.....any alternate of Filter formula in MS Excel 2016.........because some file which were made on excel 2021 also have formula issues when i open these file on MS Excel 2016.

[Gregb11]

Right, V 2016 does not have FILTER formula. I think you'd have to look at what each FILTER formula is doing to determine how (and if) to replace it.

[raeesasif2022]

I chose wrong file. see this attachment.

Dear windknife, first of all thank you for your all support.

However, in the 2nd file I did one mistake in formula in the column name "ex. gst rate" and that is division (/) instead of multiplication(x).
and when I correct this, in your solved file (Book2) the results are coming terribly wrong due to this mistake. Sorry!
Please check my last file with correction in "ex. gst rate" column formula. I hope this attached file "Book3" will be last copy for solution of my problem

thanks.

[windknife]

BA9

Please Login or Register  to view this content.

BB9

Please Login or Register  to view this content.

BC9

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter.

[raeesasif2022]

BA9

Please Login or Register  to view this content.

BB9

Please Login or Register  to view this content.

BC9

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter.

Done. Thank you.

[windknife]

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.