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

Not clear what you want to do. Which cell do you want highlighted and when?

I got that E300 must be equal to the sum of E302 and F302*50.

If you want to highlight the cell E302 when it has enough to fill the
requirement of B in F302 then choose Conditional Formatting for Cell E300 and
use FORMULA IS and the formula as
=E300(F302*50) then Green [have sufficient quantitity]
=E300<=(F302*50) then Red [need more]

"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

ok
Cell B302 is the one I want to highlight
but only when the conditions are met
As the quantities increase in E300 the conditions that allow E302 to be
highlighted will change
Also the criteria for the change is dependant on changes that can and will
occur in F302 and E302
for instance if i have 10 in F302 I should have at least 500 in E300
and the condition remains true whereas F302*50=500
so when E300 is between 500 and 550 no changes should take place
and if I have 10 in F302 but have 550 in E300 then if I put 10 as a value in
E302 making E302+F302=550 the condition becomes true again
so E302*10+F302*10 should equal 550
if at any time E302*10 and F302*10 exceed those limits, the condition
becomes false until E300 equals the next step in the process in increments of
50


"Sheeloo" wrote:

Not clear what you want to do. Which cell do you want highlighted and when?

I got that E300 must be equal to the sum of E302 and F302*50.

If you want to highlight the cell E302 when it has enough to fill the
requirement of B in F302 then choose Conditional Formatting for Cell E300 and
use FORMULA IS and the formula as
=E300(F302*50) then Green [have sufficient quantitity]
=E300<=(F302*50) then Red [need more]

"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

IF(E300=((F302*50+E302*50)),1,0)
almost works, but once the condition is met B302 should return to normal
until the condition is met again. for instance if E302=10 (500) and E300=500
the condition is met, once E300 exceeds 500 the condition is no longer valid
until it reaches 550
Basically what I want to do is to control user input until a condition is
met and give a visual clue when the conditions exist that the operation can
be performed again.
the input for E302 can only be numbers between 1 and 10 where as F302 can be
any whole number as long as E300 can cover the initail 50 per item quota. If
I have 575 in E300 and 10 in F302, I can only allow 1 more in E302 for the
condition to stay true when i reach 600 in E300 then I can allow 2 in E302
and so on.


"Sheeloo" wrote:

Not clear what you want to do. Which cell do you want highlighted and when?

I got that E300 must be equal to the sum of E302 and F302*50.

If you want to highlight the cell E302 when it has enough to fill the
requirement of B in F302 then choose Conditional Formatting for Cell E300 and
use FORMULA IS and the formula as
=E300(F302*50) then Green [have sufficient quantitity]
=E300<=(F302*50) then Red [need more]

"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

I am little bit lost, one more try:

I just guess that condition applied for e302 should be: e300=(f302+e302)*50
I believe u are adding amount in e302 to that in f302 following ur
statement: "I have 575 in E300 and 10 in F302, I can only allow 1 more in
E302 for the
condition to stay true when i reach 600 in E300 then I can allow 2 in E302
and so on"

HTH

"WavMaster" wrote:

IF(E300=((F302*50+E302*50)),1,0)
almost works, but once the condition is met B302 should return to normal
until the condition is met again. for instance if E302=10 (500) and E300=500
the condition is met, once E300 exceeds 500 the condition is no longer valid
until it reaches 550
Basically what I want to do is to control user input until a condition is
met and give a visual clue when the conditions exist that the operation can
be performed again.
the input for E302 can only be numbers between 1 and 10 where as F302 can be
any whole number as long as E300 can cover the initail 50 per item quota. If
I have 575 in E300 and 10 in F302, I can only allow 1 more in E302 for the
condition to stay true when i reach 600 in E300 then I can allow 2 in E302
and so on.


"Sheeloo" wrote:

Not clear what you want to do. Which cell do you want highlighted and when?

I got that E300 must be equal to the sum of E302 and F302*50.

If you want to highlight the cell E302 when it has enough to fill the
requirement of B in F302 then choose Conditional Formatting for Cell E300 and
use FORMULA IS and the formula as
=E300(F302*50) then Green [have sufficient quantitity]
=E300<=(F302*50) then Red [need more]

"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

Ok, can I use the (OR) function to better define the parameters in a
conditional format in conjuction with the (IF) function?

"Sheeloo" wrote:

Not clear what you want to do. Which cell do you want highlighted and when?

I got that E300 must be equal to the sum of E302 and F302*50.

If you want to highlight the cell E302 when it has enough to fill the
requirement of B in F302 then choose Conditional Formatting for Cell E300 and
use FORMULA IS and the formula as
=E300(F302*50) then Green [have sufficient quantitity]
=E300<=(F302*50) then Red [need more]

"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

same as Sheeloo, I am not sure, too. Guess u need for any additional B at
least 50 of A.
If yes, then the condition should be like: e300=(f302+1)*50

"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