Google Sheets – How to Tie an Enum to Numeric Values

formulasgoogle sheetsgoogle-sheets-arrayformula

I created a list of items Data Validation in a sheet corresponding to time-frequency:

weekly,monthly,annually

I want to multiply values in a numeric column with the corresponding weekly values to get a normalized annual cost so weekly would be 52x, monthly 12x.

It's just three columns: one numerical, one with drop down of 3 possible text values (weekly, etc) and a final numerical (annual total). If I choose weekly from the drop down, I want the value in the first column to be multiplied by 52 and the result stored in the 3rd total column.

How do I accomplish this in Google Sheets?

Best Answer

=ARRAYFORMULA(IF(B1:B="weekly";   A1:A*52;
              IF(B1:B="monthly";  A1:A*12;
              IF(B1:B="annually"; A1:A*1; ))))

Related Solutions

Google-sheets – Updating data validation for a group of cells

I'm not 100% sure what the issue is, but if it is a question of how to change the range of options in data validation:

When using Data Validation, the drop downs will update automatically if you change the source-list of values that appear.

The only time you will need to re-configure the validation is if the range of the possible selections changes, or you start using a different range/sheet to validate the input cells.

Example:

options = [1, 2, 3, 4, 5]

Drop down shows:

[1, 2, 3, 4, 5]

If you want to add a value (like 6), you will need to re-do the validation because this will change the range of the options.

Google-sheets – Change value of a cell based on a drop menu of values on a different sheet

I will guide you to the solution:

First Step

is to set the drop down menu's (combo box) on the range you want. In my example it will be in sheet1, having range A1:A10. From menu Data, choose this option:

enter image description here

Set the criteria range, to be the range in sheet2 (values in combo box):

enter image description here

Now the values will appear in the first sheet:

enter image description here

Second Step

is to add the following formula in cell B1 of sheet1:

=ARRAYFORMULA(IF(ISBLANK(A:A)=TRUE,"";VLOOKUP(A:A,Sheet2!A:B,
  {2} * SIGN(ROW(A:A)),0)))

This will automatically populate column B, with the values found in column B of sheet2, corresponding to the input given in column A of sheet1.

See the example file I've prepared: COMBOBOX and VLOOKUP

Best Answer

Related Solutions

Google-sheets – Updating data validation for a group of cells

Google-sheets – Change value of a cell based on a drop menu of values on a different sheet

First Step

Second Step

Related Topic