Using List function with hiding irrelevant outputs

[salmanjan]

Hi all, I am in a fix on how to create a formula where the user can drive the outputs by selecting specific timeframe (placeholder Cell add: C3 on worksheet named Staff Number Calculation).

Based on this selection, the output should be calculated in address C9:H25, now where I am getting stuck is referencing as this output is based on values present in worksheet named "Volumes" and "Time Spent Per Unit".

However, as volumes vary by each month, the number od workers required should also be reflective of changing in volumes worked upon.

For example:
If Annualized Year option selected = Refer to 'VOLUMES '!B4, 'VOLUMES '!C4 & 'VOLUMES '!E4
If 1st Quarter option selected= Refer to 'VOLUMES '!B9:D9, + 'VOLUMES '!B14:D14, + 'VOLUMES '!B19:D19
If 2nd Quarter option = 'VOLUMES '!B23:D23, + 'VOLUMES '!B28:D28, + 'VOLUMES '!B33:D33
likewise 3rd and 4th Quarter.............
If monthly option selected = Refer to monthly totals in Volumes worksheet e.g.,
Refer to 'VOLUMES '!B9:D9, + Refer to 'VOLUMES '!B14:D14, + Refer to 'VOLUMES '!B19:D19 and so on

I am attaching the spreadsheet as well, as it has three simple worksheets in it.

Any help will be highly appreciated.

Thanks,

[NBVC]

Have a look at what is done so far....

What I did was:

Create Named Ranges for the Annualized Year and the 4 Quarters...(AnnualizedYear, FirstQuarterOnly, SecondQuarterOnly, etc.)

Then I changed the Data Validation List to same as named ranges except left spaces... this is so that I can reference cell C1 in my formulas in B4:B6.. which sums the totals with the range specified in the drop down...using formula:

Please Login or Register  to view this content.

copied down..

then in C4, you need a simple Vlookup() to reference the correct cells in the table in 'Time Spent Per Unit' sheet and similar formulas in D4 and E4 copied down to row 6... these formulas reference the Work Items in A4:A6.


I adjusted the formula in C9 to fix the lookup table range you had...

I am not sure about the Month by Month thing though... what do you want to display in C4:E6 when that is chosen? How do you want table C9:H25 to calculate.?

[Dunc3142]

I am not sure what should change if you select first QTR or monthly.

[salmanjan]

Thanks alot NBVC.

The calculations are working fine...exactly how I wanted them to, but can we do something like this too, like if we select First Quarter Only in C3 then only row C10 is visible rest all rows C9, C11:C25 gets hidden. In this way, the person who is looking at the sheet to see how much staff is requierd will not see other outputs?

Secondly, in Month to Month thing, what I was trying to achieve was to show how many workers are required on per month basis based on work items per month, but I am assuming this can not be achieved through this sheet, but can we somehow refer to Volumes sheet and display the result in rows 14 to 25 and hiding the rest of the output rows namely 9:13.

Your help in this matter is highly apperciated...as always!

Regards,

[NBVC]

What is supposed to go in C4:C6 if Month by Month is selected?

[salmanjan]

I have realized that it cannot be showed by using the cells C4:E6, as these cells are being used to sum up for Annualized and Quarters, but am not sure how can I refer to only Volumes sheet for Jan, Feb and.other months numbers.

The objective that I am trying to achieve is to show that each month that many staff is required to process, based on fluctuating volumes.

Thanks in advance!

[NBVC]

So are we just looking at column E in Volumes and bringing back that total for each month?

Not sure exactly what needs to be filled in from Volumes when Month by Month is selected?

[salmanjan]

For month to month, for example in January it should bring the following numbers from Volumes B9:D9, for February, we should be bringing B14:D14, for March ..B19:D19.

In this way, we can show that fluctuating volumes are impacting the results in Staff Numbers calculation.

Does that make sense...sorry for confusing the issue too much, if you desire I can put up the numbers into Jan...to Dec manually what to show.

Thanks

[NBVC]

See if the attached is what you desire....

Select Month by Month and all the months come up with Totals from column E in Volumes...

Select Quarters or Annual and you get the previously devised numbers...

Hope this is good!

[salmanjan]

All is good except, when we select the Month by Month, the total number shown in C9:C20 on the worksheet named...Staff Number calculation is only taking into account the number in D9 and not of B9 and C9 for January and like wise for all months. It is only taking one volume that is present in Column D and ignoring Column B and C. Whereas it should include values of B9, and C9 as well as of D9 to get total volume.

Secondly, while going through the formulae and results something is going wrong in results in columns G9:G20 and H9:H20, while F9:F20 is coming out perfectly fine.

[NBVC]

Well, I think I fixed C9:C29... just refenced column D instead of E in the Volumes Sheet... See attached.

The problem with G9:29 and H9:29 is that I don't know what you want in D9:D29 which those other two ranges refer to? Please clarify what you want in D9:D29.. the column titles in in C8 and D8 don't seem to make sense to the data we are collecting for the Month by Month option.

[salmanjan]

Ok, now what I have done is, that I have added another worksheet named StaffNumberCalculation-MONTHLY (in red color) and have manually calculated the numbers by referecing the Worksheets Volumes and Time Spent Per Unit.

This is the output that I want to get when the user selects Month by Month option in C3.

Hope this helps....and I really appreciate your time that you have spent on my this spreadsheet. Thanks a lot.

[NBVC]

So I am once again confused... is that a new column you want added and to show values only when Month by Month is selected? Or do you want those numbers in that added column to appear in column C....?

[salmanjan]

exactly, it is a new column I want added and to show values only when Month by Month is selected. In this way, User can see how much staff is required each month and what volume is processed each month.

On a sidenote, I have noticed that when we select Annualized Option (in C3) the results are as follows:
A):
Est. Min FTEs Required 2008 = 1.023
Est. Max FTEs Required 2008 = 1.546
Est. Average FTEs Required 2008 = 1.285

However if we select the Quarters- 1st Quarter is as follows:
B):
Est. Min FTEs Required 2008 = 0.696
Est. Max FTEs Required 2008 = 0.262
Est. Average FTEs Required 2008 = 0.218

Quarters- 2nd Quarter is as follows:
C):
Est. Min FTEs Required 2008 = 1.070
Est. Max FTEs Required 2008 = 0.406
Est. Average FTEs Required 2008 = 0.337

Quarters- 3rd Quarter is as follows:
D):
Est. Min FTEs Required 2008 = 1.165
Est. Max FTEs Required 2008 = 0.440
Est. Average FTEs Required 2008 = 0.366

Quarters- 4th Quarter is as follows:
E):
Est. Min FTEs Required 2008 = 1.163
Est. Max FTEs Required 2008 = 0.437
Est. Average FTEs Required 2008 = 0.364

Essentially if we take Averages of Min, Max, and Average (of all four Quarters and then dividing it by 4) it should be as follows and should match in serial a) above and they do:
Min=1.023
Max=1.546
Avg=1.285

But based on calculations that I have listed for querters is giving me this result:
Min=1.023
Max=0.386
Avg=0.321

I am assuming in Quarter 1, Quarter 2, Quarter3, and Quarter 4, the Minimum column is referencing right values, but the Maximum and Average column is not referencing correctly.

[NBVC]

Okay check out the attached...

[NBVC]

If C9 to E9 results are correct and C24 is corrrect (don't know how you got that number) and F4 to H6 are correct, then F9:H9 are correct...

otherwise you will have to tell me where it is wrong.

...this is becoming a project for me...

[salmanjan]

It is working perfectly, fine, I do not have words to thank you! Thanks a zillion!

Secondly, a brief question......if I change the label in A28 to A33, would it throw all my formulae off, coz I tried changing one name from Annualized Year to Yearly, and it changed everything, do let me where can I make the changes so that it does not impact the whole worksheet!

Kindest regards,

[NBVC]

Yes it would throw off everything.. since the Lists in Volumes are named according to the values in A29:A34 and the drop down list depends on that name and so to the formulas (indirectly).

If you must change Annualized Year to Yearly, then you must change the Named Range "AnnualizedYear" to "Yearly" also.

To do so, go to Insert|Name|Define and select AnnualizedYear and Change name to Yearly.... then click Ok... this will actually add another Named range and the old AnnualizedYear still exists too... you can go back to Insert|Name|Define and reselect AnnualizedYear and click Delete to permanently remove.

[salmanjan]

Awesome! you are simply awesome!!

Thank you for being here!

[salmanjan]

Hello NBVC, : I tried to create a sheet using actual numbers and headings but it messed up everything, I have been trying to solve it since last three hours....but am resorting to you for help, kindly review where have I made the mess.....

Please help me in this regard.

[NBVC]

..here you go...

...just check the numbers....

[salmanjan]

Great....but there is just one problem, in the worksheet Staff Number Calculation address B4:B38 the numbers are getting doubled (double counted) from worksheet "Work Produced" Row 7, probably it is adding Row 6 and Row 7.

Can you please guide me how to correct that issue, so that I can do it myself....thanks for guidance and support.

Secondly, there seems to be some issue when adding Month to Month numbers, as it is showing January's numbers divided by 12, whereas it should only show numbers for that month. How can I present the monthly numbers divided by only one month, so that User can see how many staff are required on per month basis.

I am trying to figure out what could be the reason but could not understand. Please help me in guiding as to where could be an issue.
Regards,

[NBVC]

Your logic is very confusing to me.

I am not sure why you have an "A", "B", "C" and what the relation to the "AAA", "AAB", "AAC", etc are?

I went according to the information I found in the formulas you tried in your last attachment...

Using those formulas, it seems you are only interested in the first 3 rows of the information in "EST TIMES" sheet when getting the Month by Month information. Is that correct?

Also, in the "Work Produced" sheet, you have C Totals, but not A Totals or B Totals.. Is that the case? Or is this a sampling? The formulas in "Staff Number Calculation" Sheet, B4:B38 sum up the numbers based on the word Total being in the row header.... If you change the formula in B4 to :

Please Login or Register  to view this content.

where the part in Red defines the row to get the values from as the one where the text "C Total" appears. So if you are going to have "A Total" and "B Total" too, then you are going to have to decide (or tell me) which values are going to be summed in the main sheet.

[salmanjan]

Your logic is very confusing to me.

I am not sure why you have an "A", "B", "C" and what the relation to the "AAA", "AAB", "AAC", etc are?

It was my bad, that I left "A", "B", "C" in the sheet. I apologize for that, these should not be there, I have removed that row, as there is no relation between "A", "B", "C" and "AAA", "AAB", "AAC".

I went according to the information I found in the formulas you tried in your last attachment...

Using those formulas, it seems you are only interested in the first 3 rows of the information in "EST TIMES" sheet when getting the Month by Month information. Is that correct?

All rows of EST TIMES are relevant 3 to 37.

Also, in the "Work Produced" sheet, you have C Totals, but not A Totals or B Totals.. Is that the case? Or is this a sampling? The formulas in "Staff Number Calculation" Sheet, B4:B38 sum up the numbers based on the word Total being in the row header....

It is sampling, Actually they should be like Jan Total, Feb Total, Mar Total, Apr Total ......Dec Total, i.e., Total for each month.

If you change the formula in B4 to :

Please Login or Register  to view this content.

where the part in Red defines the row to get the values from as the one where the text "C Total" appears. So if you are going to have "A Total" and "B Total" too, then you are going to have to decide (or tell me) which values are going to be summed in the main sheet.

Considering the fact that fact that it was error on my part, the only values that are going to be summed in the main sheet will be each months totals...i.e., Jan Total, Feb Total, Mar Total etc.

I have edited the sheet with all correct information, and I aopoligize for all the confusion, I will be extra cautious in future.

Last thing, the only purpose that I want to achieve when selecting Month to Month option is to show the User/Audience that if in Jan they have lesser volumes coming in (from Work Produced" sheet, then they will require lesser number of staff as compared to say Jul..Aug. But at present each month numbers are being divided by 12, is there a way where we can show actual numbers of Staff required, when Month by Month option is selected.

Thanks a lot.....

[NBVC]

Exactly which numbers are being divided by 12? The numbers B41 to B52 are taken from the Totals column in Work Produced sheet.

Also What should the formula (in words, ie. what exactly are they supposed to do) be for C41, D41, E41?

[salmanjan]

Exactly which numbers are being divided by 12? The numbers B41 to B52 are taken from the Totals column in Work Produced sheet.

That is percetly fine, this is returning exactly the values that I want

Also What should the formula (in words, ie. what exactly are they supposed to do) be for C41, D41, E41?

In C41 the result should show the sum of the following column D3:D37 (Worksheet "EST TIMES") multiplied by D13:AL13 (Worksheet "Work Produced"), in this way, we will get the MINIMUM hours required to process the work received in the month of January 2008. For example
AAA = 400(Work Produced sheet, cell D13) X 3.25 (EST Times sheet, cell C3), +
AAB= 0 (Work Produced sheet, cell E13) X 0.00 (EST Times sheet, cell C4), + AAC = (Work Produced sheet, cell F13) 469 X 18.51 (EST Times sheet, cell C5), +
AAD = 1085 (Work Produced sheet, cell G13) X2.47 (EST Times sheet, cell C6), +
AAE = 289 (Work Produced sheet, cell H13)X4.94 (EST Times sheet, cell C7) and so on.....till.......
AAZI = 0 (Work Produced sheet, cell AL13) X 0 (EST Times sheet, cell C37).

In D41 the result should show the sum of the following column D3:D37 (Worksheet "EST TIMES") multiplied by D13:AL13 (Worksheet "Work Produced"), in this way, we will get the MAXIMUM hours required to process the work received in the month of January 2008. For example
AAA = 400(Work Produced sheet, cell D13) X 5.00 (EST Times sheet, cell D3), +
AAB= 0 (Work Produced sheet, cell E13) X 0.00 (EST Times sheet, cell D4), + AAC = (Work Produced sheet, cell F13) 469 X 32.02 (EST Times sheet, cell D5), +
AAD = 1085 (Work Produced sheet, cell G13) X5.79 (EST Times sheet, cell D6), +
AAE = 289 (Work Produced sheet, cell H13)X10.53 (EST Times sheet, cell D7) and so on.....till.......
AAZI = 0 (Work Produced sheet, cell AL13) X 0 (EST Times sheet, cell D37).

In E41 the result should show the sum of the following column D3:D37 (Worksheet "EST TIMES") multiplied by D13:AL13 (Worksheet "Work Produced"), in this way, we will get the AVERAGE hours required to process the work received in the month of January 2008. For example
AAA = 400(Work Produced sheet, cell D13) X 4.13 (EST Times sheet, cell E3), +
AAB= 0 (Work Produced sheet, cell E13) X 0.00 (EST Times sheet, cell E4), + AAC = (Work Produced sheet, cell F13) 469 X 25.27 (EST Times sheet, cell E5), +
AAD = 1085 (Work Produced sheet, cell G13) X4.13 (EST Times sheet, cell E6), +
AAE = 289 (Work Produced sheet, cell H13)X7.73 (EST Times sheet, cell E7) and so on.....till.......
AAZI = 0 (Work Produced sheet, cell AL13) X 0 (EST Times sheet, cell E37).

This will give total time required in the month of January (Minimum time in C41, Maximum time in D41, and Average time in E41).....likewise for every month.

Thanks a lot!

[NBVC]

Okay, let's hope this is the magic moment...

See attached...

Note: Previously you had mentioned dividing the Est Times sums by 60... this last time you did not... I assumed you did want the divide by 60, so I added it into the formulae in C41:E52... If that is incorrect go to C41, D41 and E41 and delete the part that says /60 from each.. then copy down... see the red part of the sample formula below.

e.g.

Please Login or Register  to view this content.

Note also for that formula to work, the number of elements in the columns of Work Produced must match the number of elements in the rows of EST Times...

For the Annualized report totals that were being doubled... well that is because you have to headers (actually 3 headers in the AnnualizedYear range (i.e. Rows 3:8 of Work Produced sheet)... and my formula looks for anything with the word "Total" in it to some up the corresponding row(s).. so what I did was change "B Total" and "C Total" to "B Tot." and "C Tot." so that there is only one row with the actual word "Total" in it...... Now the numbers in the Staff Number Calculation sheet for Annualized Year should be correct, and so should everything else...

Keeping my fingers crossed..

[salmanjan]

Let me see.... thanks..I'll quickly reply!

[salmanjan]

no...if I chose the option Annualized Yearly, it is giving me perfect answer, that I am expecting, but when I chose option Month by Month, it just gives me very different answers.....

In order to show what numbers should I get in C41, D41, and E41 I have added another sheet named "TEST SHEET FOR ANSWERS", in this sheet I have added January and February numbers for your perusal, and in Row 39, I have highlighed in RED color the results that I want in C41, D41, and E41 for January 2008 and C42, D42, and E42 for February 2008.

I am sure this will clear the confusion that I created in the first place, as for FTE numbers, the numbers I should be getting in F41, G41, and H41 are highlighted with pink background for Jan 2008.

Let me know if my this clarification is understandable.....I really feel bad that I made you work so much! but also really really appreciate your help!

[NBVC]

tomorrow...

[salmanjan]

Thanks NBVC, just a sidenote, the calculations for Annual, First Quarter, Second, THird and Fourth Quarter are fine, only the monthly one is giving me issues, the numbers that should appear in for January 2008 in D41:H41 are calculated in Worksheet "Test Sheet for Answers" G39:I39 and G45:I45.

Thanks a lot!

[NBVC]

Ok.. thanks for clarifying the results...

See Attached.. I think we've got it now.

I had to introduce the TRANSPOSE() function in order to transpose the array in the EST Times sheet to a horizontal array...

But now note that the formulas in C41, D41, E41 and down are array formulas and were therefore confirmed with CTRL+SHIFT+ENTER not just ENTER.

[salmanjan]

Bingo! wow...I am so thankful to you I can't explain in words!

Profound regards and deep appreciation.

[NBVC]

Great! You are welcome.

I am glad that's done