10-13-2020 11:14 AM
10-13-2020 11:14 AM
Aloha to All,
I'm Reggie, a US Army retiree now training US Army National Guard units. I am a member of a six-person team. I am new to this group. I am requesting assistance with my excel document that I have attached. I am not an expert in excel but I have dabbled with it for quite a while. I have learned most of it using youtube. Here is the scenario. I train units on US Army fire support operations. I am trying to create an excel file that will help calculate data. All but the last column is correct and that is where I need help.
Col A: Identifies the categories.
Col B: Identifies the types of targets within the category. HVT means high-value target. These are determined by the unit being trained.
Col A and Col B will always be commander specific. There are only six categories but there are hundreds of targets that could be considered an HVT.
Col C thru Col H: Are weighted values. Each has its own definition. One HVT is weighted by each of these letters. This is known as target value analysis and we use the CARVER acronym to weight these areas. CARVER is also weighted. See data on the right of the attached file, (the red values). Criticality may be worth more than effects and so on.
Col I: Total for that particular row. This will be a hidden column.
Col J: One is more important than two, two is more important than three. The score with the highest total value should be identified as the #1 HVT. The score with the lowest is the #18 HVT. This is only if there are 18 targets. There could only be 5. This is unit dependant. In this example, there are 18 targets.
Col K: Measures the largest value from Col I and sorts this column from the largest value in descending order. This will be a hidden column.
Col L: This is my problem column. Col L should identify all of the Col B data. However, when the total value of (Col I) has two or more values that are the same which will happen, the result in Col K showed the same HVT for all entries rather than identifying the first HVT (Scarab) and then the URAL 375.
I am a quick learner but I can’t seem to figure this out. Any help with this effort will surely lessen my stress level.
10-13-2020 12:02 PM
@Detlef Lewin A complete overhaul may be correct. This is the first time performing this type of calculating. I usually explain the data to the audience. I just ordered two excel books today to get smarter on excel. I do appreciate your comments and your quick response. I will gain a greater understanding of excel based on what you did. Thank you Mr. Lewin.