Formula help

[Dawei]

Hi,

I am a excel newbie and I need some help with a formula.

I want to find the percentage of orders delayed from a list of orders, and I want them to be divided by suppliers so I can find the percentage of orders delayed from each supplier. I thought I can use the list of orders since it has a column where you can find a time of how much each order was delayed, if it says ,"", there was no delay.

My formula from the column next to the one I am working on right now where I find out the average delay divided by supplier is the following:

=LOOKUP(9,99999999999999E+307;CHOOSE({1;2};0;AVERAGE(IF('Order statistics'!$B$2:$B$10=A8;'Order statistics'!$D$2:$D$10))))

I understand that my explanation is a bit complicated but if somebody could give me a hint to work from I would bu thankful.

I was thinking that I need to find all ,"", compare those to the orders that was delayed per each supplier and thereby find the percentage, but how to do this...

Thanks

David

 

[galileogali]

Try This change:
=LOOKUP(9,99999999999999E+307;CHOOSE({1;2};0;AVERAGE(IF('Order statistics'!$B$2:$B$10=A8;if('Order statistics'!$D$2:$D$10<>"",)'Order statistics'!$D$2:$D$10))))

GALILEOGALI

 

[Dawei]

Thank you for the reply Galileogali!

There is something incorrect in the formula but I cannot find it. Something it might be is that I do not need to use the "average". I just need the number of orders delayed as a percentage of total orders from every supplier.

This should most definitely be in there: Order statistics'!$B$2:$B$10=A8 when it recognizes the suppliers.

I do not know what this means:

if('Order statistics'!$D$2:$D$10<>"",)'Order statistics'!$D$2:$D$10

Where is the division done between delayed and not delayed orders to get the percentage?

Thank you again Galileogali!

David

 

[Brian from Maui]

If I understand,

=SUMPRODUCT(--(ISNUMBER(G2:G10),--(B2:B10=A8))/COUNTIF(B2:B10,A8)

Where G2:G10 houses true time values and B2:B10 suppliers.

 

[Dawei]

Hi Brian,

I think you understood perfectly, this is what I am looking for but..

I have used the formula you gave me and changed it according to my specifications but (as a newbie) it still returns "there is something wrong with the formula".. I cannot find anything wrong with it.

=SUMPRODUCT("--(ISNUMBER('Order statistics'!D2:D10);--(B2:B10=A1))/COUNTIF(B2:B10;A1)

Thank you for your time!

 

[Dawei]

Hi,

Just to clarify my problem..

This is the "order statistics" sheet:
B D
Supplier Delay (in days)
Manly Plant 31
Meidao 68
Whirlpool 24
Whirlpool 2
Meidao
Manly Plant 1
Trendy 5
Qingjiao 86
Whirlpool 90

This is the "order statistics" sheet:

Supplier % of orders delayed
A B
Aifeilingli
Appollo
Banker
Chenghui
Whirlpool
Ego
Everlasting
Manly Plant
Meidao
Trendy
Qingjiao


And the answers that I am looking for is for example Meidao 50%.

Thanks once again!

 

[Aladin Akyurek]

Hi Brian,

I think you understood perfectly, this is what I am looking for but..

I have used the formula you gave me and changed it according to my specifications but (as a newbie) it still returns "there is something wrong with the formula".. I cannot find anything wrong with it.

=SUMPRODUCT("--(ISNUMBER('Order statistics'!D2:D10);--(B2:B10=A1))/COUNTIF(B2:B10;A1)

Thank you for your time!

Remove the double quotes, that is, "...

 

[Dawei]

Hi,

I didnt get it to work with the formula yesterday so I elaborated a bit and I would like some help with another formula I thought might work. My issue is that I just want the total number of orders from one supplier divided by total number of delayed orders. I dont know if you need SUMPRODUCT for that..

My thought was something like this:
=COUNTIF('Order statistics'!$D2:$D10;"'Order statistics'!$B2:$B10=A2;>0")/COUNTIF('Order statistics'!$B2:$B10;A10)

Right now this formula doesnt work, but with a few alterations??

Any help would be greatly appreciated!

Thank you!

 

[Dawei]

Hi,

I might have been to diffuse of what I am looking for.

=COUNTIF('Order statistics'!$D2:$D10;"'Order statistics'!$B2:$B10=A2;>0")/COUNTIF('Order statistics'!$B2:$B10;A10)

Where 'Order statistics'!$D2:$D10 is a column with "delays (in days)" and
'Order statistics'!$B2:$B10 is a column with supplier names (where each row is a order)=A2 is referring to the supplier name on my current worksheet.

I simply want to divide the number of orders of each supplier that are delayed with the total number of orders.

Thankful for any help, I really can't find the answer, I only get zeros or division#/0..

Thank you again guys.

David

 

[Brian from Maui]

Hi,

Just to clarify my problem..

This is the "order statistics" sheet:
B D
Supplier Delay (in days)
Manly Plant 31
Meidao 68
Whirlpool 24
Whirlpool 2
Meidao
Manly Plant 1
Trendy 5
Qingjiao 86
Whirlpool 90

This is the "order statistics" sheet:

Supplier % of orders delayed
A B
Aifeilingli
Appollo
Banker
Chenghui
Whirlpool
Ego
Everlasting
Manly Plant
Meidao
Trendy
Qingjiao


And the answers that I am looking for is for example Meidao 50%.

Thanks once again!

How do you arrive at 50%? And do you have the English version of Excel?