Jump to content
Madruga_SP

Doubt about Excel

Recommended Posts

Madruga_SP

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 this post


Link to post
Share on other sites
steven-g

What is the problem?

Share this post


Link to post
Share on other sites
Madruga_SP

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.

 

Thank in advance

:?

Share this post


Link to post
Share on other sites
steven-g

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

Share this post


Link to post
Share on other sites
Madruga_SP

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 this post


Link to post
Share on other sites
steven-g

Try this formula in column AH

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

Share this post


Link to post
Share on other sites
Madruga_SP

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

 

If you don't mind could attached your worksheet, please?

Share this post


Link to post
Share on other sites
steven-g

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 this post


Link to post
Share on other sites
Madruga_SP

Awesome Steven,

 

Thank you very much.

Worked like a charm! :notworthy:

 

Regards

Share this post


Link to post
Share on other sites
steven-g

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

Share this post


Link to post
Share on other sites
Madruga_SP

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)

 

Thank in advance

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

Edited by Madruga_SP

Share this post


Link to post
Share on other sites
steven-g

Hi Madruga,

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 :lol:)

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

Share this post


Link to post
Share on other sites
Madruga_SP

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) :glare:

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.;)

 

Thank for your kindness.

:D

Share this post


Link to post
Share on other sites
steven-g

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 this post


Link to post
Share on other sites
Madruga_SP

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 this post


Link to post
Share on other sites
steven-g

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 this post


Link to post
Share on other sites
Madruga_SP

Many Thank Steven,

 

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

 

I really appreciate your help.

I struggle with Excel! :facepalm:

 

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

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

 

Regards

Share this post


Link to post
Share on other sites
Madruga_SP

Hi Steven :D

 

How are you?

 

Fantastic result!!! :thumbsup:

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.

:notworthy:

 

Kind Regards

Share this post


Link to post
Share on other sites
steven-g
8) Your welcome, I like a good challenge in excel formulas

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...