Hi all,
Hope all are well.
In attached sheet, sheet "Dashboard" is to be populated with respect to all other sheets.
Columns B to H in sheet "Dashboard" are to be populated from columns AJ to AP from all other sheets with respect to the names in column A of sheet "Dashboard", where columns under
Q1 = Jan - March
Q2 = Apr - Jun
Q3 = July - Sep
Q4 = Oct - Dec
Can someone please help with the formula
Last edited by rizwanulhasan; 10-04-2023 at 09:27 AM.
IN "Dashboard" B5
Formula:
Please Login or Register to view this content.
Copy down
Q1S is named range of months in Qtr 1: see AF:AI
You can work out the rest BUT "Attendance %" is not a simple summation (and seems to be derived on a fixed number of 31 days in each month).
Last edited by JohnTopley; 10-04-2023 at 10:32 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
In "AQ" of each month
Formula:Please Login or Register to view this content.
in "Dashboard" H5
=$B5/(91-SUMPRODUCT(SUMIFS(INDIRECT("'"&Q1S &"'!"&"AQ2:AQ20"),INDIRECT("'"&Q1S&"'!"&"A2:A20"),$A5)))
I used 91 as this is a more accurate value for days per quarter.
Hi John,
Many thanks for your response.
I managed to get the data using your below formula for Quarter 1.
Formula:Please Login or Register to view this content.
However, i couldn't do it for Quarter 2. It shows error as #NAME?
Sorry i am not good at excel. So could you please et me know what should be modified in formula to get Quarter 2 results?
Also, you are right about this. Its my mistake. I am sorry.
It shouldn't be 31 but infact it should be no. of days in a month. Could you please guide how to fix thatOriginally Posted by JohnTopley
You need to define the named ranges Q2S:Q4S- see "Name Manager" for Q1S definition.
For "Attendance %"
Formula:Please Login or Register to view this content.
Many thanks John. Name Manager done.
However below formula gives 0%
Formula:Please Login or Register to view this content.
See attached:January and February
Oops. So that was meant to be added in each sheet. Will do it.
Just 1 query. The formula excludes cells containing "Weekoff". How can i also exclude cells containing "NA" in same formula?
How about formula in H5 of sheet dashboard? Do i need to use your below formula?
Formula:Please Login or Register to view this content.
For "NA"
Formula:Please Login or Register to view this content.
H5 formula: copy down column as per file i Sent
John, i used above formula under AQ but that gives incorrect percentage. See sheet March.
To rectify percentage attendance in each sheet, I tried excluding "NA" in below formula but it gives SPILL error:
Formula:Please Login or Register to view this content.
Can you please let me know how to correct above formula?
Also, H5 in sheet dashboard contains number 91 thus giving an estimate percentage. Can it be modified to exact no. of days in that quarter
Last edited by rizwanulhasan; 10-04-2023 at 02:12 PM.
See post #9! and formula is correct in March but does not include NA!
I'll look at exact days later!
Yeah, formula in post #9 is correct for column "AQ", however that doesn't rectify the Attendance percentage under column "AP" of each sheet.
Perhaps putting "NA" in below formula will do the work. I tried but gives SPILL error
Formula:Please Login or Register to view this content.
In "AH2" of EACH month sheet
Formula:Please Login or Register to view this content.
in "Dashboard" H5
Formula:Please Login or Register to view this content.
Thank you so much John. All's perfect now
John, sorry but 1 issue.
Row 21 in sheet "Dashboard" shows name "Q" which joined in March and has 12 days present out of his 12 working days of March and therefore % under column H in sheet "Dashboard" should be 100% instead of 17%.
Same with % under column AP in sheet March. It shows 44% instead of 100%
FYI - Some persons will join and leave and may not be present in all months. Perhaps i should have said this earlier. Sorry.
Can you please have a look
Last edited by rizwanulhasan; 10-04-2023 at 04:29 PM.
I just checked formula. Your below formula is rightly considering present days (column B in dashboard) and non working days (column AQ from each sheet) yet percentage is incorrect in sheet dashboard and under columns AP in each sheet. I am confused
Formula:Please Login or Register to view this content.
Last edited by rizwanulhasan; 10-05-2023 at 01:42 AM.
Why are they incorrect ? what do you think the values should be?
With no file, I cannot offer any help.
My reading of the formula(s) gives correct %s. Do the AP/AQ formula use the COUNTIFS formula to include "NA" as per Post #12?
Last edited by JohnTopley; 10-05-2023 at 01:23 PM.
I attached file in post # 15. Let me reattach here and explain about %.
In sheet March, person Q joined from 16-03-2023 and was present 12 days (AJ18). The working days were AH2 - AQ18 i.e. 31-19 = 12. So in that case % in AP18 (Sheet March) and H21 (Sheet Dashboard) both should be 100%.
Hope i was able to explain.
In ALL monthly sheets:
In AH
Formula:Please Login or Register to view this content.
Copy down
in "Dashboard"
in H5 and copy down
Formula:Please Login or Register to view this content.
I note in the file you have not changed the formula in AQ: why do I bother ?
Its perfect now. Thanks again John.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks