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

 

[Dawei]

Hi Brian,

Sorry, I am being unclear. What I am looking for is not suppliers % of total delays. I am looking for how many % of each suppliers orders that are delayed. That is, in Meidaos case, one order delayed and one order delivered in time, 50% of Meidaos orders are delayed.

Sorry for that.

And thanks for the answer.

Dave

 

[Brian from Maui]

From your example, try,

=SUMPRODUCT(--(A2:A10=A3),--(B2:B10=""))/COUNTIF(A2:A10,A3)

If you don't have an English version of Excel, you'll have to have this translated. If not, why are you using ; and extra "?

 

[Dawei]

Thanks Brian! That worked!

Yes I am using a Swedish version of Excel which means you need to use ( instead of (,).

So my formula looks like this:
=PRODUKTSUMMA(--('Order statistics'!B2:B10=A10);--('Order statistics'!D2:D10=""))/ANTAL.OM('Order statistics'!B2:B10;A10)

That is:

=SUMPRODUCT(--('Order statistics'!B2:B10=A10),--('Order statistics'!D2:D10=""))/COUNTIF('Order statistics'!B2:B10,A10)

I just have one more problem to go! If one supplier have 3 orders connected to them and they are all late I get the result 0%. And if all orders were in time I get 100%.

This is obviously because the first part of the formula looks for orders that were in time and which means if all were late the result will be 0. Perhaps I need Excel to look for all orders that were late instead?

Thank you very much Brian!