How to figure percentage of on time delivery

[On the grind]

I am an excel rookie and need to display the percentage of on time delivery on a spread sheet. I have the if/then figured out. This figures out and displays if the delivery was "on time" or "late" and they are put in a cell. But what I also need at the end, or bottom of the spreadsheet is the percentages of "on time" or "late". It looks something like this:

Days to deliver.....On time or late?
10....................On time
15 ......................Late
10.....................On time
7 ......................On time
21 ........................Late
etc. ........................etc.

This spreadsheet has around 100 results, currently. How do I write a formula (that also looks nice) to tell me what percentage out of all deliveries was on time? Or late? Of course these values are all in cells, so lets just call the title row "1" and the columns "A" and "B". (I know this is going to come out sloppy, but it's supposed to be two columns).

So I want to take the amounts of "on times" - "lates" divided by the total instances (right?). How do I write this?

Also, this is a perpetual spreadsheet; I am going to continued adding more data. Can it be written so the formulas don't have to keep being written over and over for the new cells?

Any quick help would be appreciated. Thank you.

 

[daniels012]

Try:
=counta(A1:A25)-countif(B1:B25,="ON TIME")

Michael

 

[daniels012]

OOOpps!

leave out the "=" equal sign!
Michael

 

[On the grind]

Didnt work...

The result was "2". I was looking for a percentage of "on time". The deliveries are "on time" when they are delivered in less than 15 days.

It should have came out 60% on time. Can I write this formula in the same column as the value for days it took to deliver?

Also, I am going to be adding more and more values and "on time" or "late" to the spreadsheet. How can the formula be written so these cells are included, without having to rewrite the formula for the added cells? Or is this not possible?

Also, how could I make it so it is displayed "Percentage of on time deliveries: 60%"? For aesthetic value.

Thanks again for any help.

 

[daniels012]

Opps!
This should work!
="Percentage of on time deliveries are "&(COUNTA(F5:F14)-COUNTIF(G5:G14,"<>on time"))*10&"%"

michael

 

[daniels012]

This might be better for your formula after looking at it:

=COUNTIF(G5:G14,"on time")/COUNTA(F5:F14)*100

Both should work though.

Michael

 

[On the grind]

Thanks, Michael. I would never have gotten that.

The first formula came up at 30%, when it should have been 60%, however.

The second formula came out at 60.

I liked how the first formula came out, as far as what was displayed. "Percentage of on time deliveries are 30%".

Could you correct the 1st formula so it can come out correctly for me.

Also...no way to add cells and make the formula continue working without re-writing it?

Thanks again!

 

[On the grind]

Micheal,

I got it to come out to 60%. I should have tried before I posted again....

But what about adding cells and not changing the formula, or not needing update the formula? Is there a way this can be done automatically?

Thanks again. Big help.

 

[daniels012]

="Percentage of on time deliveries are COUNTIF(G:G,"on time")/COUNTA(F5:F14)*100 &"%"

Put this in a cell other than column G!

Michael

 

[On the grind]

I got a circular reference error.

Remember I'm a rookie so if there is anything you are taking for granted I know...well, I probably don't.

Also...I'm assuming the G:G was to cover anything in that column. What about the other column? Column A has the result (on time or late), and Column B has the data. I will be entering more in both columns...

Thanks.