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.

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

 

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Jun 11, 2023

ExcelExciting Thank for for the solution, very helpful.

Resources