Help with "IF" function

[Thor27470]

Hello,

For starters I am a beginner with excel so forgive me if I come across stupid!

Let's pretend below is my excel sheet, I want a formula/function to show that if my "Actual" figure is less than my "Target" figure it will show the "Result" figure as a negative number and the colour red and likewise, if the "Actual" figure is more than my "Target" figure it will show the "Result" figure as a positive number and the colour green.

Any ideas please?

Thanks

Target 100 100
Actual 50 120
Result -50 20

[KOKOSEK]

Welcome on the Forum.

	A	B	C
1	Target	100	100
2	Actual	50	120
3	Result	-50	20

	A	B	C
1	Target	100	100
2	Actual	50	120
3	Result	=B2-B1	=C2-C1

Colouring set by Conditional Formatting. You can find it on Home/Styles section / Conditional Formatting / Highlight cells rules / Greater Than and Less Than then you can choose colour.

[TMS]

Or, you could use a Custom Number Format:

Please Login or Register  to view this content.

Format | Format Cells... | Number (tab) | Custom (at the bottom) | Type: (box) [Green]#,##0;[Red]-#,##0

[KOKOSEK]

Please Login or Register  to view this content.

Very useful. I did not know about it.

[TMS]

@KOKOSEK: You're welcome. Thanks for the rep.

I was only really aware of it in relation to red for negative values but I decided to experiment and it worked

[TMS]

@Thor27470: Thanks for the rep

[KOKOSEK]

I was only really aware of it in relation to red for negative values but I decided to experiment and it worked

I was also try something else and it appear that [Blue] also works. So it looks like excel knows some colours by name.

[TMS]

@KOKOSEK: see this great page on formatting: https://exceljet.net/custom-number-formats

Nice section on formatting with colours.

[KOKOSEK]

exceljet.net in general is great. Thanks.