How can I setup a multiplier formula with data in the same column?

Andrew Ryback

Hi everyone,

I'm looking for some help generating a formula and am not sure where to start, or what type of formula I need.

I've used Smartsheet to build a budgeting template for my team. Within the budget, we have pre-set labor rates ("base pay") that are listed by position. In my labor rate column, we also have rates for time-and-half and double-time.

Is there a way to auto-calculate the 1.5x and 2x pay, based on the crew position's base pay? The caveat here is that all positions are listed in the same column, and all labor rates are listed in the same column.

For example:

If my base pay for Production Assistant is $300 (hypothetically row 4), I want row 5 (time-and-1/2) to populate $450 and row 6 (double time) to populate $600. If I change the base rate to $250, then the 1.5x and 2x rates would change based on the base pay.

We may add or delete row labor positions in the sheet, so I can't tie these formulas to a specific cell - it would need to be some time if lookup based on the labor position and base pay.

Thanks!

Gillian C

Hi @Andrew Ryback

I think you are wanting to use INDEX MATCH or INDEX COLLECT, but it's hard to be sure from your description. is there a way you can put a sanitised version of your sheet as a screen grab to be able to see what it looks like?

Andrew Ryback

@Gillian C - I've attached a sample screenshot.

When I enter a number in UNIT COST, ROW 2, I want row 4 to multiply by 1.5 and row 6 to multiply by 2.

But the row numbers may change (as I will likely have more positions listed), so I think this needs to be an index collect formula based on the 'position / resource' column.

Gillian C

Hi @Andrew Ryback

I can't think of a set of formulae that would work for you for the above without tailoring it everytime.

If the above screenshot is to be able to view the information then you might want to think about using a row report? I've mocked up a set of screengrabs to show what I mean (I don't know the context of your data so I have made lots of assumptions 😁)

My suggestion involves having 2 sheets and a report.

Sheet 1: A sheet that lists each position/resource and the base unit cost …so the equivalent to your row 2 here.

(I've added random Position/Resources)

Sheet 2: Contains the list of names assigned to these roles and at which rate they are assigned (the 1, 1.5, 2 before multiplying by the base unit cost noted above)

The column 'Unit Cost' in the above screenshot uses the following formula to match the Position/Resource with the Unit Cost from the sheet above before then multiplying it by the 'Rate'

=(INDEX({Unit cost list Range 1}, MATCH([Position/Resource]@row, {Unit cost list Range 2}, 0)) * Rate@row)

This formula uses Index Match (see https://help.smartsheet.com/function/index )

Then set up the Row Report to summarize everything to show as below:

It's not the way you had tackled the issue but would still give you the output you need, whilst potentially make it easier to keep up to date.