| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
setting maximums or minimums
I am setting up a spreadsheet to develop transportation quotes. I need the
following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#2
|
|||
|
|||
|
Do I understand you to want to multiply A1 * B1,
And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#3
|
|||
|
|||
|
yes that is correct. Thanks.
"Ragdyer" wrote: Do I understand you to want to multiply A1 * B1, And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#4
|
|||
|
|||
|
Now what if I have a minimum but no maximum? what would that formula look
like? "Ragdyer" wrote: Do I understand you to want to multiply A1 * B1, And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#5
|
|||
|
|||
|
Try this:
=MAX(140,A1*B1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... Now what if I have a minimum but no maximum? what would that formula look like? "Ragdyer" wrote: Do I understand you to want to multiply A1 * B1, And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#6
|
|||
|
|||
|
Cool thanks for all the help!
"Ragdyer" wrote: Try this: =MAX(140,A1*B1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... Now what if I have a minimum but no maximum? what would that formula look like? "Ragdyer" wrote: Do I understand you to want to multiply A1 * B1, And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
|
#7
|
|||
|
|||
|
Thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emerald_dragonfly" wrote in message ... Cool thanks for all the help! "Ragdyer" wrote: Try this: =MAX(140,A1*B1) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "emerald_dragonfly" wrote in message ... Now what if I have a minimum but no maximum? what would that formula look like? "Ragdyer" wrote: Do I understand you to want to multiply A1 * B1, And never have the product fall below 140, Or be greater then 475? If that's the case, try this: =MAX(140,MIN(475,A1*B1)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "emerald_dragonfly" wrote in message ... I am setting up a spreadsheet to develop transportation quotes. I need the following items if possible. 1. How do you get a cell to default to a number if the calculated cost falls below the minimum cost? 2. How do you hvae a cell default to a maximum cost if the calculated cost exceeds the maximum? Example: Column A shows a rate. There is a stipulated minimum of 140. There is a maximum total cost of 475. How do you set up this formula? Column A Column B Column C Rate units cost 122.00 1 140.00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
| Fit Selection Setting Doesn't Hold | Excel Discussion (Misc queries) | |||
| show macro security setting in a cell | Excel Worksheet Functions | |||
| Setting macro security level to "low" permanently? | Excel Discussion (Misc queries) | |||
| Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) | |||
Linear Mode
