Conditional Formatting
 

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
Therefo 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

Conditional Formatting
 

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


"WavMaster" wrote:

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
Therefo 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

Conditional Formatting
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"WavMaster" wrote:

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
Therefo 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

Conditional Formatting
 

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

"Shane Devenshire" wrote:

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


"WavMaster" wrote:

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
Therefo 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

Conditional Formatting
 

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
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"WavMaster" wrote:

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
Therefo 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

Conditional Formatting
 

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

"Shane Devenshire" wrote:

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


"WavMaster" wrote:

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
Therefo 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