I am trying to change the way I run a report for a Covid tracker. The CDC has changed the way they do county-level reporting to values of "Low", "Medium", and "High" instead of percentages. I have a map built that shows case severity based on the percentages, but I now need to change that to be based on the specified values but I do not want to have to change the way I get the map data.

What I am asking is... How do I get the values of "Low", "Medium", and "High" to equal a specific Percent?

CodePudding user response：

If I read your question correctly, you can simply use nested IF-statements. To clarify I make the assumption that your old map is on the sheet named "Old-sheet" and that your map starts in cell A1. Add a new sheet to your workbook and enter the following formula into the topmost and leftmost cell (A1) where your new percentage map is supposed to start:

=IF(Old-sheet!A1="Low",0.1,IF(Old-sheet!A1="Medium",0.5,0.9))

Select cell A1 and drag it some cells to the right (to the right end of your old map) then select all cells with content and drag them down, just so your formula is in every cell you need.

Adjust the fractions (percentages) in the given formula and if applicable the format of the cells and that should be it.

CodePudding user response：

Another answer was to use nested if statements. An easier way to do it is with the IFS formula.

=IFS(old!A1="Low",.1,old!A1="Medium",.5,old!A1="High",.9,TRUE,"Invalid")

Basically it resolves one condition at a time and returns the value after the first True value. I put the last TRUE item in there (alternatively 1=1 should work) as a catch all in case it is an entirely different value. I believe this function is only available on 365 though.