Survey analysis

Status
Not open for further replies.
D

dummybhatia

New Member
Joined
May 24, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

Requirement for column "D":
  • Check for the same "Area code" in column "A"
  • If only single area code in the "A" Column then result in column "D" should come "No records for comparison"
  • If there are 2 same area codes then in column "D" against 1st area code result should come as "1st Visit" then for 2nd area code result should come as difference of population for 2nd survey compared to 1st survey date.
  • If more than 2 surveys for same area code then for 1st area code result should come as "1st visit", against 2nd no. of area code difference of population as compared to last survey date, against 3rd no. of area code difference of population as compared to 2nd survey date.
  • Same above process should get replicated for multiple entries.
Requirement for column "E":
  • Check for the same "Area code" in column "A"
  • If only single area code in the "A" Column then result in column "E" should come "No records for comparison"
  • If there are 2 same area codes then in column "E" against 1st area code result should come as "1st Visit" then for 2nd area code result should come as difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list.
  • If more than 2 surveys for same area code then for 1st area code result should come as "1st visit", against 2nd no. of area code difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list, against 3rd no. of area code difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list as compared to last survey date.
  • Same above process should get replicated for multiple entries.
Book1
ABCDEFGHIJK
1Area codeSurvey datePopulationResult expected(Manual)Formula requriedResult expected(Manual)Formula requriedHoliday list
21299211-04-202378,84,56,751No records for comparisonNo records for comparisonS.NoDateStatus
38880233100093212-04-20231,19,90,51,4621st visit1st visit102-04-2023Holiday
43174412-04-202324,67,88,90,321No records for comparisonNo records for comparison208-04-2023Holiday
53175313-04-202315,40,15,32,301No records for comparisonNo records for comparison309-04-2023Holiday
68980233100274112-04-202367,44,46,4451st visit1st visit416-04-2023Holiday
73161316-04-202375,53,62,976No records for comparisonNo records for comparison522-04-2023Holiday
853966819-04-20231,23,06,70,213No records for comparisonNo records for comparison623-04-2023Holiday
91299322-04-202389,51,44,100 No records for comparison No records for comparison730-04-2023Holiday
101286922-04-202389,31,01,090No records for comparisonNo records for comparison807-05-2023Holiday
118980233100305430-04-20233,46,54,56,789No records for comparisonNo records for comparison913-05-2023Holiday
128880233100027430-04-20233,36,65,57,880No records for comparisonNo records for comparison1014-05-2023Holiday
138980233100306105-05-20233,34,66,56,7801st visit1st visit1121-05-2023Holiday
143174607-05-202316,54,03,29,410No records for comparisonNo records for comparison1227-05-2023Holiday
1511178409-05-202367,65,67,575No records for comparisonNo records for comparison1328-05-2023Holiday
168980233100356013-05-202386,54,45,505No records for comparisonNo records for comparison1404-06-2023Holiday
171312416-05-20231,33,32,47,251No records for comparisonNo records for comparison1510-06-2023Holiday
183171027-05-202397,74,33,455No records for comparisonNo records for comparison1611-06-2023Holiday
193175627-05-20231,75,68,34,512No records for comparisonNo records for comparison1718-06-2023Holiday
208880233100093205-06-202317,56,79,08,34516,36,88,56,88343("Difference in survey days-holidays during that period")1824-06-2023Holiday
213176408-06-202318,67,84,67,120No records for comparisonNo records for comparison1925-06-2023Holiday
228980233100306113-06-202312,56,03,90,0909,21,37,33,31030("Difference in survey days-holidays during that period")2002-07-2023Holiday
233176113-06-202324,79,03,48,190No records for comparisonNo records for comparison2108-07-2023Holiday
248980233100274114-06-202317,65,05,32,90016,97,60,86,45550("Difference in survey days-holidays during that period")2209-07-2023Holiday
2510010715-06-202364,47,66,769No records for comparisonNo records for comparison2316-07-2023Holiday
2610041817-06-202317,53,49,01,100No records for comparisonNo records for comparison2422-07-2023Holiday
2711753828-06-202317,54,42,10,580No records for comparisonNo records for comparison2523-07-2023Holiday
2853770505-07-202312,45,63,40,231No records for comparisonNo records for comparison2630-07-2023Holiday
298880233100093228-07-202318,45,33,31,19188,54,22,84642("Difference in survey days-holidays during that period")2706-08-2023Holiday
308980233100305505-10-202318,54,23,61,191No records for comparisonNo records for comparison2812-08-2023Holiday
312913-08-2023Holiday
323020-08-2023Holiday
333126-08-2023Holiday
343227-08-2023Holiday
353303-09-2023Holiday
363409-09-2023Holiday
373510-09-2023Holiday
383617-09-2023Holiday
393723-09-2023Holiday
403824-09-2023Holiday
413901-10-2023Holiday
424008-10-2023Holiday
434114-10-2023Holiday
444215-10-2023Holiday
454322-10-2023Holiday
464428-10-2023Holiday
474529-10-2023Holiday
484605-11-2023Holiday
494711-11-2023Holiday
504812-11-2023Holiday
514919-11-2023Holiday
525025-11-2023Holiday
535126-11-2023Holiday
545203-12-2023Holiday
555309-12-2023Holiday
565410-12-2023Holiday
575517-12-2023Holiday
585623-12-2023Holiday
595724-12-2023Holiday
605831-12-2023Holiday
615907-01-2024Holiday
626013-01-2024Holiday
636114-01-2024Holiday
646221-01-2024Holiday
656327-01-2024Holiday
666428-01-2024Holiday
676504-02-2024Holiday
686610-02-2024Holiday
696711-02-2024Holiday
706818-02-2024Holiday
716924-02-2024Holiday
727025-02-2024Holiday
737103-03-2024Holiday
747209-03-2024Holiday
757310-03-2024Holiday
767417-03-2024Holiday
777523-03-2024Holiday
787624-03-2024Holiday
797731-03-2024Holiday
Sheet1
Cell Formulas
RangeFormula
D20D20=C20-C3
D22,D29D22=C22-C13
D24D24=C24-C6
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Survey analysis (Urgent help required)
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Also a duplicate to: Survey analysis

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Upvote 0
Status
Not open for further replies.

Similar threads

D
  • Question
Survey analysis
Replies
2
Views
179
Fluff
Fluff
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
110
etaf
etaf
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
214
POLACOLOCO
P
M
  • Solved
Formula Help.
Replies
3
Views
81
Fluff
Fluff
R
  • Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
97
etaf
etaf

Forum statistics

Threads
1,215,129
Messages
6,123,212
Members
449,090
Latest member
bes000

Share this page

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