# Formula help

#### Dawei

##### New Member
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
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
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
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
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)

#### Dawei

##### New Member
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!

##### MrExcel MVP
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)

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

#### Dawei

##### New Member
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
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
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?

Replies
2
Views
198
Replies
2
Views
1K
Replies
5
Views
289
Replies
0
Views
126
Replies
0
Views
327

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.

### Which adblocker are you using?

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

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