Conditional Formatting

[WavMaster]

I am having a problem creating a formula for the following criteria:

I have 3 cells that depend on user input, the results of that input
determine the action of the conditional formating of another cell.
Whereas the 3 cells that required user input can equal any whole number from
0 to infinity.
the first cell determines a given quantity of an item "Item A"
the second cell is the quantity of another item "Item B"
the third cell is the number of Item B that is needed for the operation,
whereas I need to have a certain quantity in Item A in order to have a
certain quantity in Item B
Meaning that if I want 1 of Item B, I need to have 50 of Item A
Once I have 1 of Item B, I will require 50 more in Item A
The conditional format will indicate when I have aquired enough of Item A in
order to obtain more of Item B.
Therefore if the condition is false, nothing changes until the quantities
are in the acceptable ranges.
I can make the formula work somewhat but in reverse of what I wish to do
To make it easier to understand, I will use the following example:

Given the following:
Item A = Cell E300
Item B = Cell F302
Quantity required = Cell E302

If the quantiy required of Item A is 1 then I need 50 of Item A
Therefore: If E302=1 then E300 must equal 50, however if I already have 1 of
Item B then E300 will have to equal 100 and so on.

=IF(E302=1,((E300=50)+(F302*50)),0)
=IF(((E302=50)+(F302*50)),E302=1,0)
both of these require an input from E302 before they will work

 

[Shane Devenshire]

Hi,

I don't follow the logic of this
"If the quantiy required of Item A is 1 then I need 50 of Item A"

 

[Shane Devenshire]

Hi,

I see I am even more confused if I read on:

"If E302=1 then E300 must equal 50, however if I already have 1 of
Item B then E300 will have to equal 100 and so on"

In other words if E302=1 E300 must equal 50 but it must equal 100 also since
E302 is alreay 1.

Please clarify.

 

[WavMaster]

I need 50 of Item A to get 1 of Item B

Hi,

I don't follow the logic of this
"If the quantiy required of Item A is 1 then I need 50 of Item A"


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Here is my guess as to what you might need:


In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=(E302+F302)*50<>E300
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=(E302+F302)*50<>E300
5. Click the Format button and choose a format.
6. Click OK twice

 

[WavMaster]

ok, the logic of the equation remains constant, while the paremters may change
for instance in order to aquire 1 of "A" I will need 50 of "B", hence 1A=50B
ok with that being said "A" is how many I have "C" is how many I want so "C"
also equals 50 B therefore 1A+1C=2B (50+50=100)
if i have 10 of "A" then I have 500 of "B"
if I have 600 of "B" and 10 of "A" then I can get 2 more of "A" represented
as 2 of "C" hence 10A+2C=12B
that is the matematical formula that will determine what happens in the cell
I wish to control, But if "C" 575 then I can only have 10 of "A" and 1 of
"B" until I have 600 of "C" at which point I can have 10 of "A" and 2 of "B"
and so on