Mapping from text to value in Google sheet equation
I have a Google Spreadsheet with two sheets in it. In one of them I have a table representing a mapping from text to a number like this:
| Text | Factor |
|---|---|
| Yearly | 1.0 |
| Quarterly | 4.0 |
| Monthly | 12.0 |
| Weekly | 52.0 |
| Daily | 365.0 |
In the second sheet I have a large table that has values like this: | Value | Billing | | --- | ---- | | 150.0 | Yearly | | 99.0| Quarterly | | 1.25 | Monthly |
Now I want to calculate a yearly value with a calculation like this:
=150.0*YEARLY+99.0*QUARTERLY+1.25*Monthly
by defining an equation in my Google sheet and specifying the range of the value and billing cells since it is a long table. Is this at all possible? I know that I can easily just add the mapped value instead in the table, but these values are only a simple example, and there will be more mapped values later that will change with time. Instead of changing those factors manually one by one in the table, this solution with a mapping in a separate sheet would allow me to easily adjust this in the calculations.
With the help of XLOOKUP to convert the text values to their correlative number, you can then use SUMPRODUCT to multiply both columns and sum the result:
=SUMPRODUCT(D2:D,XLOOKUP(E2:E,A2:A,B2:B,))
