excel 2016 formula to compare qtys

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?

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

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?

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?

let me add some manual data to the worksheet & repost it so you understand better...

attached is the worksheet with some dummy data to help you understand.

You forgot to attach it.

sorry.. now attached
compare-qtys_2.xlsx

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.

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")))

Select allOpen in new window

Firstly, I formatted cells so that positive numbers will show the positive signs too.

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.

Here is the file I worked on: compare-qtys_2.xlsx

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

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)

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.

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.

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