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 ...
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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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 08, 2023

SergeiBaklan Thank you, that's very helpful 🙂

Resources