Conditional Formatting in Rows Resulting from Formulas

Question

Hello,

This might be somewhat of a long/confusing one.

I am trying to figure out how to better automate categorization for blood pressure categories when being placed into a sheet. For example, in the image below the column "BP" is where the initial number is input. I then put a LEFT formula in the column "Sys formula" to collect the systolic number and a RIGHT formula in the column "Dia formula" to collect the systolic number.

I have been trying to apply conditional formatting to assess the individual values for where they fit in blood pressure categories (ex. BP with top number <120 and bottom <80 would be categorized as "Normal" while top numbers between 120-129 and bottom numbers <80 would be considered "Elevated." The conditional formatting is not appearing for the columns that have been split from the original BP column, as the BP column should be highlighted different colors based on category. I tried copying over "Sys formula" to the "Sys" column by inputting "=[Sys formula]@row" but the formatting still did not work with these columns.

In another experimental sheet, I simply typed in the numbers I was looking to categorize and the formatting worked just fine.

I know that theoretically I could just input the Systolic and Diastolic numbers in separate columns moving forward, however I am still struggling with how to show categorization on the primary sheet without having to navigate to a separate report or metrics sheet. I tried typing out a combination of formulas to categorize the BP but I wasn't getting the desired results for this either, as multiple outcomes were being translated to the individual cells.

If anyone has any advice/feedback or has experienced something similar, I would be happy to hear about it!

Paul Newcome · Answer

The issue is with the RIGHT and LEFT functions. By default, the output from these functions is a text string. It may look like a number, but it is not. When you manually type a number, it is stored as a number. This is why it did not work on the formulas but did for the manually entered test.

To make the RIGHT and LEFT functions output values that are stored (and can be used) as number, simply wrap them each in a VALUE function.

=VALUE(RIGHT(………..))

=VALUE(LEFT(………….))

There are also a couple of things that may cause issues with yout IF formula as well. If you can outline the desired logic, I'd be more than happy to help with that piece too.

For example, you will never get "Elevated" as an output because you have an argument for Sys Formula being less than or equal to 120 and Sys Formula greater than or equal to 129 at the same time. It is impossible for a number to be less than 120 and greater than 129 at the same time, so that argument will never resolve to true.

hello_aj · Answer

When you intentionally add + (plus sign) do you intend to show multiple results. Plus sign is considered as concatenate, they join strings of characters if not all are numbers.

If you just want to show 1 result, you wrap all in an IF statement. I would like to help if you have chart/table o