Let’s say I want to write a spreadsheet that has a label as a variable, for example I take a person’s annual salary and divide by how often they get paid to get their paycheck amount. For reasons not important here there is already a label associated with each worker: weekly, biweekly, or monthly. If the worker is labeled ‘weekly’ divide the annual salary by 52, ‘biweekly’ divide by 26 and ‘monthly’ divide by 12. I want to use a general formula so I can just copy&paste.
So far my only idea is:
Assume the label is in column A and salary is in column B. Column C Row 1 is
=B1/(IF(A1=“weekly”,52,0)+IF(A1=“biweekly”,26,0)+IF(A1=“monthly”,12,0))
Is there a more elegant way to do this especially if I have a lot more than 3 labels?
In this formula, the number of pay periods in the array portion of the Index function have to match up to the order of the labels in the array portion of the Match function exactly.
The VLOOKUP route is still the better way to go. It’s easier to make changes and any errors would be more obvious.
Be careful OP. The word “label” has a technical meaning in Excel that is not at all what you’re using it for. So you can confuse yourself something fierce if you start searching Excel help or googling for “label”.
What you mean in generic computerspeak is that you have a column which contains an indicator or a property or an attribute of the employee. The purpose of the column is to store some fact about each employee. In this case what pay check cycle he/she is on. You’ve chosen to use the words “weekly”, “biweekly”, or “monthly”. You could just as well have chosen coded values like 1, 2, 3 or A, B, C.
The smart computerish way to use an arbitrary attribute like this to either select a value or to select a calculation is a lookup table. That way provides a neat “connector” between the two ideas that can be reconfigured more easily as your needs grow or change.
As **Bean Counter **just above said. He explained the what. I’m trying to address the why.
Agree with the above, especially the use of vlookup instrad of index match. It also would allow you to easily add other payment schedules like daily or quarterly should the need ever arise.
There can be situations where a big nested if function is the more elegant solution, but this doesn’t seem to be one of them.
Whether you want to include the lookup table on the tab with the calculations or on a separate tab is up to you, but I generally prefer to keep such things on a separate tab so I don’t have to mess with defining print areas and so things stay looking “clean.”
Not knowing the end use of the spreadsheet, and just based on what you said, I’d probably approach it this way:
Tab 1 summary sheet tallying up relevant bits from the calc sheet, like how many and what % of employees are paid by each method.
-Tab 2 - List of employees with salary, status, and paycheck amount calc.
Tab 3 - your lookup table or tables
tab 4+ - any other sources of data you’re pulling from.
Properly labeling your tabs and possbly changing your tab colors can help with keeping it clear. For example, I’d probably gray out the data table tab label because it’s not something anybody but you needs to see. And even if you don’t think a summary tab is necessary, put it in, because soneone will likely ask you how many employees are at each status. It will take you 5 minutes - just countif and sumif functions.
ETA - I would probably change Bean Counter’s Vlookup formula to define the whole columns as the table array so you can jst add stuff without changing the calc formulas.
Really? Because “label” means a word in Excel as opposed to “value” or “formula” and that is exactly how I am using it e.g. “weekly” is a word thus it is a label in MS Excel.