Forum Discussion

ABSomiah's avatar
ABSomiah
Copper Contributor
May 27, 2025

Help with a Form and Formulas

Hello Team,

I have a challenge that I hope the gurus on this forum can help me solve.

Challenge 1: I have created an Excel form to be used by a group of parents, including myself, on mobile devices, sometimes simultaneously, to calculate the cost we must pay for childcare. My calculations are not adding up as expected.

Challenge 2: I'd also like to collate the kids' names on a separate worksheet as parents complete the form. Is this feasible?

I have recorded a screencast below to explain my challenge, and I will be happy to include a copy of my Excel document if required. Please point me in the right direction on what to do.

I appreciate any help you experts can provide, and I have attached a copy of the file.

https://go.screenpal.com/watch/cThtXnn6yuE

Many thanks for considering my request.

2 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    So IF you can make a few assumptions that calculation should work.  In your case your dates were 6th - 31st which starts on a Mon and ends on a Fri and was 20 days.  So just divide by 5 to get 4 weeks and multiply by the weekly totals.  BUT that assumes a) no weekday holidays, b) the start and end dates result in exact weeks.  Otherwise you must either do it day by day or account for partial weeks at the start and end and any holidays inbetween.

    As for the 2nd issue, your setup really won't create a roster as you have it.  As soon as the next parent changes the values the 'roster' would also get changed/removed.  So I would recommend instead having that 'Name of Child' filed go BELOW the pricing and restructured as a 'Sign Up List' so you have all the information for a child on a single line.  So the 'simpliest' might be:

    Child Name,   Breakfast Monday, Breakfast Tues, .... , AfterSchool A Monday, AfterSchool A Tues, ....  AfterSchool B Mon, AfterSchool B Tue, ...

    where A and B are the 2 different time options

    Alternatively you could make it easier to enter but make the calculations a little more tricky:

    Chlid Name, M, T, W, Th, F

    and undeer each day the options would be None, Breakfast only, AfterSchool A only, AfterSchool B only, Breakfast + AfterSchool A, Breakfast + AfterSchool B

    then to make calculations easier I would create a table on the side (or in a Named Lamda Function) to convert those options to True/False valuse for Breakfast, AfterSchool A, AfterSchool B.  So a table might look like:

    Option                                     Breakfast,            AfterSchool A,           AfterSchool B
    None,                                       0                            0                        0
    Breakfast only,                             1                             0                        0
    AfterSchool A only,                         0                             1                        0
    AfterSchool B only,                         0                             0                        1
    Breakfast + AfterSchool A,                   1                            1                       0
    Breakfast + AfterSchool B                    1                            0                        1 

    and then you can use XLOOKUP (or VLOOKUP) to get out the corresponding values

     

     

    Finally all this said, you need to consider that there is NO security here and you need to consider if there is any privacy concerns and issues with parents making mistakes that would affect other student information.  So parent A lists Billy for x, y, z

    then parent B goes on the list and accidentally deletes y or adds something or changes something on Billy's line.

    So although you can do this I have to question the value of it and potential issues with it.

  • I think you have to calculate the totals for each of the day of the week separately.

    For example, for breakfast club on Monday:

    =IF(D21="", 0, (NETWORKDAYS.INTL($K$12, $K$15, "1011111")-NETWORKDAYS.INTL($K$13, $K$14, "1011111"))*D21)

    And for Tuesday:

    =IF(D22="", 0, (NETWORKDAYS.INTL($K$12, $K$15, "1101111")-NETWORKDAYS.INTL($K$13, $K$14, "1101111"))*D22)

    etc.

     

Resources