excel 2016 formula to compare qtys

Frank .S
Frank .S used Ask the Experts™
on
hi experts, id like some help with my attached excel 2016 worksheet.
I need the differences table to show the differences of each row in all 3 tables;
- job 1, job 2, job 3
- the comparison will be always against job 1, so job 1 wlll be compared with jobs 2 & 3
and express them with a "tick" symbol if the same as job 1, if negative to be shown in red font with the difference and in black font if greater than job 1 with the difference.
compare-qtys_1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there! :)

I would like to confirm your requirement so do correct me if I am wrong.

For example for Job 2, in cell J7, if B7=G7, show a tick? In cell J7, if B7>G7, show difference in red font? In cell J7, if B7<G7, show difference in black font?
Frank .SBuilding Estimator

Author

Commented:
hi noah, for job 2,
= if E7=J7 show a tick, otherwise if E7 is a negative show difference in red text with a negative in front ie -1.50, or if E7 is greater than J7 then show the difference in black text with a plus sign before ie + 1.50
.
the same applies for job 3, always being compared with job 1
- is that clearer
NoahHardware Tester and Debugger

Commented:
Okay, one more time.

For Job 2, all the output will be shown in column J?

B7=G7, show a tick?
B7>G7, show difference in red font with a negative sign?
B7<G7, show difference in black font with a positive sign?
NoahHardware Tester and Debugger

Commented:
Edit the ranges in here to match your requirements

For Job 2, all the output will be shown in column J?

B7=G7, show a tick?
B7>G7, show difference in red font with a negative sign?
B7<G7, show difference in black font with a positive sign?
Frank .SBuilding Estimator

Author

Commented:
let me add some manual data to the worksheet & repost it so you understand better...
Frank .SBuilding Estimator

Author

Commented:
attached is the worksheet with some dummy data to help you understand.
NoahHardware Tester and Debugger

Commented:
You forgot to attach it.
Frank .SBuilding Estimator

Author

Commented:
sorry.. now attached
compare-qtys_2.xlsx
Analyst Assistant
Commented:
Try this:

1 Put this formula in Q7 and copy down.

=IF(E7=J7, "✔",E7-J7)

2 Put this formula in R7 and copy down.

=IF(E7=O7, "✔",E7-O7)

3 Select the columns/rows you've entered the above formulas in.

4 Goto Format>Conditional formatting...

5 Select New rule...Format only cells that contain...

6 Select Greater than or equal to, enter 0 for the value and format as green font.

7 Select New rule...Format only cells that contain...

8 Select Less than, enter 0 for the value and format as red font.
compare-qtys_3.xlsx
Frank .SBuilding Estimator

Author

Commented:
thanks norie, can i ask how you add the tick symbol into the ifstatement?
- i have checked & it seems that your attached wksht produces errors in columns Q&R if there are not values in columns E or J or O.
- can we have the formula in col Q&R not show anything unless there is a value in column e and either column j or column o.
NoahHardware Tester and Debugger

Commented:
Hi there!

I have tried my best and unfortunately, I couldn't put the tick sign since we need to change the font to Wingdings and if I change the font Wingdings all the numbers will be Wingding symbols instead. In that case, I used "okay" instead which is why the formula looks like this now.

Example:
=IF(E7=J7,"OKAY",IF(J7>E7,E7-J7,IF(E7>J7,E7-J7,"NA")))

Open in new window


Firstly, I formatted cells so that positive numbers will show the positive signs too.
Capture.PNG
Then I made a few conditional formatting rules to ensure that the color comes out right the way we want it if it is positive or negative.
Capture1.PNG
Here is the file I worked on: compare-qtys_2.xlsx
Rob HensonFinance Analyst

Commented:
Another alternative with much simpler options.

In columns Q and R use these formulas:
=IF(OR(E7="",J7=""),"",IFERROR(E7-J7,""))
=IF(OR(E7="",O7=""),"",IFERROR(E7-O7,""))

then apply custom format to columns Q and R:
[Green]+0.00;[Red]-0.00;[GREEN]"✔"

Custom format has four options separated by semi colon
1) positive numbers  [GREEN]+0.00   will display in green with a + symbol and 2 decimal places
2) negative numbers  [RED]-0.00   will display in red with a - symbol and 2 decimal places
3) zero  [GREEN]"✔"  will display a green tick
4) text - omitted in this sample

To get the tick in the Custom format I just copied and pasted from an existing character.

See attached.
compare-qtys_2.xlsx
Rob HensonFinance Analyst

Commented:
Looks like don't need the IFERROR in the formula as blanks which would cause an error are already covered:

=IF(OR(E7="",J7=""),"",E7-J7)
=IF(OR(E7="",O7=""),"",E7-O7)
Rob HensonFinance Analyst

Commented:
Took the Green in the Custom format from the formatting of the sample sheet, looks like you want positive or tick in black. If so, just remove the [GREEN] sections of the Custom format.

Base format of the cell should be General, whatever Font and Font size you want and Automatic Font colour.
Frank .SBuilding Estimator

Author

Commented:
hi Rob Henson, thankyou but i have a few comments;
- the green font colour used is hurting my eyes when i look at it, how can i change the shade?
- also there is no formula in cell R27, which needs to be formatted the same as columns Q & R.
Rob HensonFinance Analyst

Commented:
the green font colour used is hurting my eyes when i look at it, how can i change the shade?

Unfortunately, a bit limited to basic colours in Custom format and can't change shade. As mentioned in previous comment, if you remove the [GREEN] bit from the Custom format it will stay with the Automatic colour or you can then format whatever colour you want, the Red cells will still turn Red when required.

there is no formula in cell R27, which needs to be formatted the same as columns Q & R.

Just copy and paste from row 26

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial