PDA

View Full Version : Excel formula



Myth
27-02-2010, 03:17 PM
As part of my job, I am required to maintain stock levels
AS part of this I have an excel page with all our stock part numbers, and the current levels. I also have a column which has a suggested order point (taking into account lead-times of suppliers etc)

What I want to do (without enabling macros) is this:
If the balance in column A5 is less than the balance in A8, then either:
1, highlight the background for the entire range A1-A8 (preferred); or
2, highlight column A5 in red bold font (least preferred); or
3, have some red ** in column A9

Is this possible in excel?

pcuser42
27-02-2010, 03:25 PM
It's possible with conditional formatting in Excel 2007.

Myth
27-02-2010, 03:29 PM
I think we use Excel 2003

We connect to the server via thin clients, so I can't really get 2007 installed
This is why I also asked for no macros - they have us reasonably locked down

pcuser42
27-02-2010, 03:33 PM
I have little experience with Excel 2003, but it's in there too. http://office.microsoft.com/en-us/excel/HA011116611033.aspx

Jen
27-02-2010, 04:29 PM
I've done this in Excel 2002/2003 using conditional formatting. You can only have two conditions though. No macros were required.

I had to use an additional column (cell) that contained a formula that generated a result eg Y or N. Your Y or N would be if the balance in column A5 is less than the balance in A8. So if A5 is less than A8, then display a "Y", otherwise display a "N".

I then used conditional formatting so that if an Y was the result, then make the Y font white (so it was invisible). If an N was present, then make the N font bold and red. This made a very obvious alert.

I couldn't say if this cell = N, then change the background (or font) in a different cell or range of cells. You can only apply the rule to the cell you are in. Hope that makes sense.

You should add the conditional formatting to whatever column does the maths for A5 and A8.

nofam
27-02-2010, 04:38 PM
Conditional formatting will do the job nicely - just bear in mind that you're limited to only 3 levels of formatting in Excel 2003 and older; there's no constraint in Excel 2007, but too many conditions will slow your spreadsheet down considerably.

You can get around the 3 level limit in older versions by using CASE statements in VBA, which is nice and easy to do.

Myth
27-02-2010, 05:25 PM
OK, what is wrong with this.. IF(H4 <= K4) when added to a "If Formula" conditional format dialog?

I am quite inexperienced when adding formulae to excel

TeejayR
27-02-2010, 05:45 PM
You should be able to use this is Excel 2003

Select all the cells in the range you wish to highlight (sat A1:A10) and enter the conditional formula =A$5<A$8 - the trick is to make the rows fixed so all rows in the range you wish to highlight are looking at the levels in rows 5 and 8. Then enter your display my favourite is a red fill background and a white bold font.

And whenever a5 is less than A8 cell A1:A10 are displayed as white font on red fill

You can then copy this condition across multiple columns - the test is done on the rows.

Trev

Myth
27-02-2010, 06:22 PM
Thank you... that works :)

Now, if I were to take it further... If I want do this:
=A$5<A$8
=A$6<A$6
=A$7<A$7
=A$8<A$8
and so on; is there an easier way to do this?

Also, if I want the word ORDER to appear in another column if the conditions are met, or nothing to be in the column if the conditions aren't met, how do I add this?

TeejayR
27-02-2010, 06:41 PM
Thank you... that works :)

Now, if I were to take it further... If I want do this:
=A$5<A$8
=A$6<A$6
=A$7<A$7
=A$8<A$8
and so on; is there an easier way to do this?

Also, if I want the word ORDER to appear in another column if the conditions are met, or nothing to be in the column if the conditions aren't met, how do I add this?

Something wrong with your tests eg you cant test A$7<A$7 because they are the same cell

For ORDER your formula would be =If(A$5<A$8,"ORDER","")

Trev

Myth
27-02-2010, 07:24 PM
Thank you... that works :)

Now, if I were to take it further... If I want do this:
=A$5<A$8
=A$6<A$6
=A$7<A$7
=A$8<A$8
and so on; is there an easier way to do this?


sorry,
that should have been:
=A$5<D$5
=A$6<D$6
=A$7<D$7
=A$8<D$8

Also: =A$5<A$8 format works
but when I add the () with the true false it fails to do anything

TeejayR
27-02-2010, 07:47 PM
Formula should be

=IF(A$5<A$8,"ORDER","")

but this is in another cell not in a conditional format

Trev

TeejayR
27-02-2010, 07:50 PM
sorry,
that should have been:
=A$5<D$5
=A$6<D$6
=A$7<D$7
=A$8<D$8

Are you wanting to test all these conditions and do something if they are all true

- if so you can use the AND formula

Trev

Myth
27-02-2010, 08:23 PM
IM wanting to test conditions on a line by line basis

because A$4 might be less than D$4 but B$5 might be great than D$5

I dont want them all coming up as red becasue only onwe line meets the conditions (if you know what I mean)

TeejayR
28-02-2010, 07:50 AM
As part of my job, I am required to maintain stock levels
AS part of this I have an excel page with all our stock part numbers, and the current levels. I also have a column which has a suggested order point (taking into account lead-times of suppliers etc)

What I want to do (without enabling macros) is this:
If the balance in column A5 is less than the balance in A8, then either:
1, highlight the background for the entire range A1-A8 (preferred); or
2, highlight column A5 in red bold font (least preferred); or
3, have some red ** in column A9

Is this possible in excel?

Sorry I just misunderstood point 1

You can achieve this by taking the $ (prior to the row number) out of the test - you also need to make sure there are no quotes around the formula.

I just tested this by doing the test on cell A5 then using the format painter to copy the conditional test to the next cell a6 - I then copied cell a6's format to a7 and so on. This won't be a problem if you only have to test a few rows but if you have to test 100's p[erhaps you could consider flipping your row and column layouts

Trev

Myth
28-02-2010, 08:51 AM
Umm, I think we are both lost lol

Disregard the first post for now, as I have it highlighting A5 (as an example) when it meets the condition (it's value is lower than D5)

Now, what I want to achieve is - with one command, it is possible to have this conditional format applied to A6, A7, A8 and so on if their values are lower than their relevant comparison cells

i.e.
with one command for the table, can I highlight:
A5 if its value is lower than D5, else don't highlight
A6 if its value is lower than D6, else don't highlight
A7 if its value is lower than D7, else don't highlight
A8 if its value is lower than D8, else don't highlight
etc etc..

or do I need to do a command for each line?

TeejayR
28-02-2010, 11:30 AM
I set up a spreadsheet with the following columns
A=Product number
B=Weekly Sales
C=10 weeks Sales
D=Stock on Hand
E=Order ?

First I created the Order test

In cell E5 I tested if cell D5 was less than c5 if not I created the word Order
using the formula =IF(D5<C5,"Order","")

I then selected cells A5:E14 and created the following conditional formula

=$E5="Order" and for the "Applies To" range =$A$5:$E$14 if you use the red fill with a white font the columns A:E (for the appropriate rows) are highlit when you need to order your stock

If you only want to make the "Order" highlight then you set the "Applies To" range to be $E$5:$E$14

Hope this helps

Trev

Myth
28-02-2010, 03:04 PM
Sweet,
Thank you for your help. :D

I made a slight mod, and now it works how I want

TeejayR
28-02-2010, 06:47 PM
Glad to hear it

Trev