Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Unsure?
 
Posts: n/a
Default change cell value greater than another cell value using formulas

A+B=C
Say that A and B must equal 10. For A, you input calculations using other
formulas and get that A=6, so automatically B must equal 4. However, say that
as a constraint, B has to equal 7 or greater. How do you make that cell
change the value in order to impose this new constraint either using formulas
or another method but not changing the values manually?

(If B is 5 for example, we order 7. But if B is 8 we leave it at 8.)

Thanks


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bill Kuunders
 
Posts: n/a
Default change cell value greater than another cell value using formulas

In cell B1 enter =IF(AND(A13,A1<10),7,IF(A1=10,0,10-A1))
The variable is in A1 not in B1
B1 is the result of the calculation.
If A1 is greater then or equal to 10 I assume you do not want to add any.

you could store the values 7 and 10 in seperate cells sp that if there is
any change to the condition
you only need to change those cells
E1=7 (min order quant)
F1=10 (req stock)
formula is now
=IF(AND(A1F1-E1,A1<F1),E1,IF(A1=F1,0,F1-A1))

--
Greetings from New Zealand
Bill K
"Unsure?" wrote in message
...
A+B=C
Say that A and B must equal 10. For A, you input calculations using other
formulas and get that A=6, so automatically B must equal 4. However, say
that
as a constraint, B has to equal 7 or greater. How do you make that cell
change the value in order to impose this new constraint either using
formulas
or another method but not changing the values manually?

(If B is 5 for example, we order 7. But if B is 8 we leave it at 8.)

Thanks




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif formulas change after doing a sort Bob Smith Excel Worksheet Functions 3 January 4th 06 12:17 AM
How do I change a cell range with a reference cell? Danneskjold Excel Discussion (Misc queries) 2 August 11th 05 07:37 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Cell references change when entering new data [email protected] New Users to Excel 2 May 6th 05 07:48 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"