Specific Formula for IF OR argument incorporating Absolute Value

[DomByrne01]

Dear Users,

Some help with an excel formula please.

I have recurring 3 columns across a spreadsheet;

In comparing columns A and B the third column is a variance. I want to use the variance absolute value to reassign a value in column A that reflects column B and generate a new table on a separate worksheet;

Data File

Column A Column B Variance
47.0% 86% -39%
100% 90% 10%
0% 100% -100%
100% 0% 100%


The request is as follows;

- IF the absolute value of the variance is > 0% or < 100% THEN assign the % of column B into column A

AND /OR

- IF the absolute variance = 100% then highlight the cell in red



The required output;

Column A Column B Variance
86% 86% 0%
90% 90% 0%
0% [RED] 100% -100%
100%[RED] 0% 100%

Is there someone out there who can assist me in writing a formula that will support this request?



Regards DomByrne01

[FDibbins]

Hi, welcome to the forum

If I understand you correctly, you want to replace a value in column A with what is shown in column B, given certain criteria?

If that is what you want, you cannot do that with formulas - they only work on the cell they reside in, and cannot affect any other cells.
You will need VBA (code) for this.