Formula help

Dawei

New Member
Joined
Jul 17, 2007
Messages
36
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

galileogali

Well-known Member
Joined
Oct 14, 2005
Messages
748
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

New Member
Joined
Jul 17, 2007
Messages
36
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

New Member
Joined
Jul 17, 2007
Messages
36
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

New Member
Joined
Jul 17, 2007
Messages
36
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

New Member
Joined
Jul 17, 2007
Messages
36
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

New Member
Joined
Jul 17, 2007
Messages
36
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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?
 

Forum statistics

Threads
1,181,364
Messages
5,929,547
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top