Forum Discussion

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Jun 08, 2023
Solved

Help with a formula which uses parameters based on which day / date it is.

Dear Excel Community,

 

I was hoping you could assist me in creating a formula for the below conundrum ?

 

I have attached a workbook in which i need to work out costings for events. The costing vary depending on if the event occurs between Monday - Saturday or Sunday / Bank Holiday.

In the "Variables" sheet, you will see the different rates applicable.

 

What i would like to achieve is a formula that will work out the resourcing costs for an event based on the day of the event. However, it should also factor in bank holiday dates and charge these as a Sunday / Bank holiday rate.

 

I would like to apply this to cells F8, H8, J8 & L8 within the "April" sheet. (Resourcing section which i have highlighted).

 

The bank holiday dates are also listed in the "Variables" sheet.

 

Thanking you in advance.


Raj

 

 

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 08, 2023

    Mr_Raj_C 

    I'd convert variables to structured tables. With that formula for Resources -Staffing block could be

    =E8 *
    IF(
        ISNA(MATCH($B8, BankHolidays[Bank Holidays], 0)) *
            ($C8 <> "Sunday"),
        INDEX(
            RegularRate[Cost / Hourly Rate],
            MATCH(E$6, RegularRate[Resource], 0)
        ),
        INDEX(
            HolidayRate[Cost / Hourly Rate],
            MATCH(E$6, HolidayRate[Resource], 0)
        )
    )

    The rest is similar.

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Jun 08, 2023

    Hi Mr_Raj_C 

     

    As suggested by SergeiBaklan the good way is to use a structure table, if you do not have a structure table you may use  a range reference and you can use the below formula

     

    =(
    IF(
        (WEEKDAY($B8, 1) = 1),
        VLOOKUP(E$6, Variables!$D$8:$E$11, 2, FALSE),
        IF(
            ISNUMBER(MATCH($B8, Variables!$G$6:$G$15, 0)),
            VLOOKUP(E$6, Variables!$D$8:$E$11, 2, FALSE),
            VLOOKUP(E$6, Variables!$A$8:$B$11, 2, FALSE)
        )
    )
) * E8

     

    I'm attaching both files, remember using an unstructured table file you need to expand and adjust the range. While with the structured table you need to just update the new information at the last row.

     

    Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget to mark it as Official/Best Answer to help the other members find it more

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 08, 2023

    Mr_Raj_C 

    I'd convert variables to structured tables. With that formula for Resources -Staffing block could be

    =E8 *
    IF(
        ISNA(MATCH($B8, BankHolidays[Bank Holidays], 0)) *
            ($C8 <> "Sunday"),
        INDEX(
            RegularRate[Cost / Hourly Rate],
            MATCH(E$6, RegularRate[Resource], 0)
        ),
        INDEX(
            HolidayRate[Cost / Hourly Rate],
            MATCH(E$6, HolidayRate[Resource], 0)
        )
    )

    The rest is similar.

    • Mr_Raj_C's avatar
      Mr_Raj_C
      Brass Contributor
      Jun 11, 2023

      SergeiBaklan I used the proposal you gave and i needed to make some changes. I was hoping i could add another charge (Mobile operator) to the list and expand the list but it hasn't worked (the one you created is locked). I even tried to create a new list and then point the formula to that but that hasn't worked.

       

      Are you able to modify the attached for me to accommodate the new charge ? I note your previous comment about structured tables. Unfortunately i don' have the expertise to implement this.

       

      Thanking you in advance,

       

      Raj

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 11, 2023

        Mr_Raj_C 

        In file you attached data for Resource variable is organized in structured table.

        When you add new data at the bottom, in general such table shall be expanded automatically. Not sure why that didn't happen in your case.

        However, you may resize structured table manually by dragging bottom right corner of the table.

        Or stay within table, on ribbon Table Design->Resize table

        Same for holidays plus change duplicated Mobile Driver on Mobile Operative

        With that formula works

         

         

Resources