## Recommended Posts

Hi guys!

I have doubt about excel formula.

Can anyone help me out, please?

I don't know if I off topic.

Thank you

##### Share on other sites

What is the problem?

##### Share on other sites

Thanks for replay steven,

I'm trying to solved that problem.

My english is really poor, and I don't know if I able to explain my doubt.

I'll try to take more time solving that. If I didn't, I let you know.

##### Share on other sites

Don't worry about the language, we will manage, try screen shots, or post an example excel file.

##### Share on other sites

So I've attached the worksheet and I'll try to explain my task.

First of all I'd like to find minimum price of my products.

But I couldn't because the formula recognize the number 0 (zero) as a minimum. In fact isn't true, zero is null, the company that there wasn't price is because doesn't exist that product.

The last column (valor Mínimo) find the minimum price. So I'd like to fix that column to doesn't recognize zero as minimum.

The company 4 doesn't have that product, so the price is null (zero).

The right answer for the last column is the company 1 price is 1.600,00.

Sorry for my poor english.

MODELO COTAÇÃO (7 FORNEC.).xlsx

##### Share on other sites

Try this formula in column AH

=SMALL(F9:AG9,COUNTIF(F9:AG9,"<=0")+1)

##### Share on other sites

Thank you for replay steven,

Each company has 2 column named "desconto" and "IPI" in fact that value will be the minimum value.

I'd like to ignore that value "desconto and IPI", and get just the total value in each company and put the minimum value in AH (Valor Minimo).

##### Share on other sites

In that case try the following formula

=SMALL((I9;M9;Q9;U9;Y9;AC9;AG9);SUMPRODUCT(COUNTIF(INDIRECT({"I9";"M9";"Q9";"U9";"Y9";"AC9";"AG9"});"<=0"))+1)

MODELO COTAÇÃO (7 FORNEC.).xlsx

##### Share on other sites

Awesome Steven,

Thank you very much.

Worked like a charm!

Regards

##### Share on other sites

Seu bem-vindo. I told you we would get to the answer

##### Share on other sites

Hi Steve!

How are you doing?

I've made a little modification the worksheet and I need some help.

The modification was when the company there aren't the products appears "NC".

My second request is the column AH 62 put the lower price without "NC".

I mean, I want to that column just show me the lower price, but with all products.

If all the companies has at least one "NC" show a message "NLP" (no low price)

MODELO COTAÇÃO (7 FORNEC.) TESTE.xlsx

##### Share on other sites

Ok here just a bit cold, temperatures are just above freezing,

I could read your question in two ways, so I have added 2 solutions (I just hope one of them is correct )

MODELO COTAÇÃO (7 FORNEC.) TESTE.xlsx

##### Share on other sites

Hi Mr. Steve,

Thank you for the quickly replay.

Each Company has the total of the itens in row n°61.

In the column AH and row n° 61, show me the the best price (lower price).

(I'd like to change just the AH -61 for that situation)

What's I'm trying to do is get the lower price with all the products.

If any company hasn't one of them (NC), replacing to other one that has all of it and if all company has at least one (NC), none of them will be aproved (NLP)

I hope have explained better my request.

Sorry for my poor English.

##### Share on other sites

This is just to try to help me understand.

You have 7 companies that offer products ?

There are 3 products in the list?

3 different companies have the lowest price for each product?

So in row 61 for each company if they do not have at least one product that has the lowest price then it should show "NLP"?

Could you do an example to show when and where NLP should appear

##### Share on other sites

Steven,

I attached an example.

The company has the lowest price is n°7. But the company doesn't have 3 products -NC. (lowest price is false)

If one of the seven company get at least one NC, is not true lowest price. You have to considering if all the items is quoting. (company n°1 or n°3)

So, The column AH - 61 have to get the lowest price with discount. (row named valor liquido)

If all the companies have at least one NC, the column AH-61 appears NLP (no lowest price)

Regards

Example.xlsx

##### Share on other sites

And this? I added a couple of helper formulas on row 64, but this I hope gives you the result you want in AH61

Example.xlsx

##### Share on other sites

Many Thank Steven,

I'll take a look tomorrow and I let you know.

I struggle with Excel!

By the way, It is really hot here in Brazil.

Don't rain for a couple of weeks, it's terrible.

Regards

##### Share on other sites

Hi Steven

How are you?

Fantastic result!!!

It's impressive man!! Couldn't be better

I'm really happy with the goal. It was better than I thought.

thank you very much for your patience and kindness.

Kind Regards

##### Share on other sites
Your welcome, I like a good challenge in excel formulas

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.