SOLVED

Question related to Formulas of 3 sheet in Formula.xlsx workbook

Iron Contributor

Hello Everyone, 

 

In COURSE SHEET, there is a question -

Q1 >>> How many have neither REGISTERED nor COMPLETED any of the 3 courses?

Q2 >>> How many have REGISTERED or TRAINING in atleast 2 of the 3 courses?

Q3 >>> How many have not been TRAINED in any of the 3 yet?

 

 

In ADD SHEET, there is a question -

Question >>> If start row is 10 and end row is 21 then sum would be 907

 

 

In PRODUCTION SHORTAGE SHEET, there is a question -

Question >>> Find out the set of PART NUMBER that are contributing to 80% of the production shortage?

 

Please help..???

 

Here is the attached file in which I have written questions in Red highlighted..

 

 

5 Replies

@Excel 

Homework?

 

I'd use two helper columns. See the attached version.

@Hans Vogelaar 

I don't understand question #2.

For question #3:

Sort the data in descending order on the shortage column.

Select the items from the top until the sum is more than 80% of the total.

Unselect the last item, then select the next one.

Etc.

You'll soon find a solution.

@Hans Vogelaar 

sir, what is SELECT column? And why we write SELECT column?

And how it will come 1 or 0?

like - 

Screenshot (3945).png

best response confirmed by allyreckerman (Microsoft)
Solution

@Excel 

A 1 in the Select column indicates that this row is included in the set.

A 0 indicates that the row is not included.

The formula in D33 calculates the sum of all included shortages.

I started by entering 0 in all rows, then replaced them by 1 from the top, as described in my previous reply.

Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Excel 

A 1 in the Select column indicates that this row is included in the set.

A 0 indicates that the row is not included.

The formula in D33 calculates the sum of all included shortages.

I started by entering 0 in all rows, then replaced them by 1 from the top, as described in my previous reply.

View solution in original post