Excel formula help!!!

JB2385

New Member
Joined
Apr 26, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Good Day,



I am working on a spreadsheet that is tracking a large quantity of vehicles that are being built and inspected. I have formulas counting the total number vehicles, total number of vehicles remaining, Total number of vehicles built and total number of vehicles inspected. I have used the the =CountA formula to assist in tracking that information. I also have to track total percentage completed and total percentage remaining. This is were it gets tricky for me. When 1 vehicle has been built it is considered to be 90% complete and once it had been inspected it now 100% completed and ready to be sent to the customer. Can anyone help me with a formula that will take in account for the 90% complete (vehicle built) and 10% complete (vehicle inspected) and display it in the percentage complete and percentage remaining? I have a small example spreadsheet here below. I used 4 vin numbers to represent each vehicle. For simple math when 1 vehicle is built and inspected it should display 25% complete and 75% remaining. If only one vehicle is built but not inspected it should display 22.5% and once the vehicle gets an inspection date it should increased the percentages by 2..5% to give me the full 25%. The spreadsheet below is just a small example of my master spreadsheet that is going to contain about 130 vehicles. I hope this is not confusing for anyone. Please let me know if you have questions.


Example1.xlsx
ABC
1VINBuild DateInspection Date
2JH4DA9468LS80718111/23/202311/24/2023
3SCBBA63Y5EC01888411/23/2023
41FD0X5HT6GEA4583311/23/202311/24/2023
51FTWX32P04EB3537511/23/202311/24/2023
6
7Total Number of VehiclesTotal Number RemainingPercentage Complete
840
9Total Number BuiltTotal Number InspectedPercentage Remaining
1043
Sheet1
Cell Formulas
RangeFormula
A8A8=COUNTA(A2:A5)
B8B8=A8-A10
A10:B10A10=COUNTA(B2:B5)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C8Other TypeDataBarNO
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
How about?:

Libro1.xlsx
ABC
1VINBuild DateInspection Date
2JH4DA9468LS80718111/23/202311/24/2023
3SCBBA63Y5EC01888411/23/2023
41FD0X5HT6GEA4583311/23/202311/24/2023
51FTWX32P04EB3537511/23/202311/24/2023
6
7Total Number of VehiclesTotal Number RemainingPercentage Complete
84097.50%
9Total Number BuiltTotal Number InspectedPercentage Remaining
10432.50%
Hoja1
Cell Formulas
RangeFormula
A8A8=COUNTA(A2:A5)
B8B8=A8-A10
C8C8=A10*0.9/A8+B10*0.1/A8
A10:B10A10=COUNTA(B2:B5)
C10C10=1-C8
 
Last edited:
Upvote 1
Solution
How about?:

Libro1.xlsx
ABC
1VINBuild DateInspection Date
2JH4DA9468LS80718111/23/202311/24/2023
3SCBBA63Y5EC01888411/23/2023
41FD0X5HT6GEA4583311/23/202311/24/2023
51FTWX32P04EB3537511/23/202311/24/2023
6
7Total Number of VehiclesTotal Number RemainingPercentage Complete
84097.50%
9Total Number BuiltTotal Number InspectedPercentage Remaining
10432.50%
Hoja1
Cell Formulas
RangeFormula
A8A8=COUNTA(A2:A5)
B8B8=A8-A10
C8C8=A10*0.9/A8+B10*0.1/A8
A10:B10A10=COUNTA(B2:B5)
C10C10=1-C8
The Formula works great with this spreadsheet. When I tried to transcribe the information and tweak the formula to fit my tracker it didn't work correctly. Only thing I adjusted because there are more vehicles was the cell numbers in the formula. I made a simple tracker thinking I would be able to transfer the formula with minor changes but it didn't work. Below is a copy of what the actual tracker will look like with some information in it like previous post. Some cell locations have changed but again I thought I could just transfer the information and make the adjustments and be good. I'm sure I might be doing something wrong. I appreciated the help!

Tracker Example.xlsx
ABCDEFGHIJ
1TRACKER
2Company VINMODELBUILD DATEBUILD W/O NUMBERINSPECTION DATETESTING W/O NUMBERComments
3JH4DA9468LS80718129-Dec-202329-Dec-2023
4SCBBA63Y5EC01888429-Dec-2023
51FD0X5HT6GEA4583329-Dec-2023
61FTWX32P04EB3537529-Dec-2023
71FTWX32P04EB3537529-Dec-2023
81FTWX32P04EB3537529-Dec-2023
91FTWX32P04EB3537529-Dec-2023
101FTWX32P04EB3537529-Dec-2023
111FTWX32P04EB3537529-Dec-2023
12JH4DA9468LS80718129-Dec-2023
13SCBBA63Y5EC01888429-Dec-2023
141FD0X5HT6GEA4583329-Dec-2023
151FTWX32P04EB3537529-Dec-2023
161FTWX32P04EB3537529-Dec-2023
171FTWX32P04EB3537529-Dec-2023
181FTWX32P04EB3537529-Dec-2023
191FTWX32P04EB3537529-Dec-2023
201FTWX32P04EB3537529-Dec-2023
21JH4DA9468LS80718129-Dec-2023
22SCBBA63Y5EC01888429-Dec-202329-Dec-2023
231FD0X5HT6GEA4583329-Dec-202329-Dec-2023
241FTWX32P04EB3537529-Dec-202329-Dec-2023
251FTWX32P04EB3537529-Dec-202329-Dec-2023
261FTWX32P04EB3537529-Dec-202329-Dec-2023
271FTWX32P04EB3537529-Dec-202329-Dec-2023
281FTWX32P04EB3537529-Dec-202329-Dec-2023
291FTWX32P04EB3537529-Dec-202329-Dec-2023
30JH4DA9468LS80718129-Dec-202329-Dec-2023
31SCBBA63Y5EC01888429-Dec-202329-Dec-2023
321FD0X5HT6GEA4583329-Dec-202329-Dec-2023
331FTWX32P04EB35375
341FTWX32P04EB35375
351FTWX32P04EB35375
361FTWX32P04EB35375
371FTWX32P04EB35375
381FTWX32P04EB35375
39SCBBA63Y5EC018884
401FD0X5HT6GEA45833
411FTWX32P04EB35375
421FTWX32P04EB35375
431FTWX32P04EB35375
441FTWX32P04EB35375
451FTWX32P04EB35375
461FTWX32P04EB35375
47
48Total Vehicles44Total Vehicles Built30Percentage Complete
49Total Remaining14Total Vehicles Inspected12Percentage Remaining
Tracker
Cell Formulas
RangeFormula
B48B48=COUNTA(B3:B46)
B49B49=B48-D48
D48D48=COUNT(D3:D46)
D49D49=COUNT(F3:F46)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F48Other TypeDataBarNO
F49Other TypeDataBarNO
 
Upvote 0
Thank you Felixstraube! After rechecking the formula I made a couple newbie mistakes while adjusting the formula. Thanks for all the help and the formula did in fact work perfectly.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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
Back
Top