Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am needing to add a SUM formula to a cell to do the following: Cell B2 is
number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? I'm running Windows XP SP2 with Excell 2000 ver. 9.0.2720 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am needing to add a SUM formula to a cell to do the following: Cell
B2 is number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? D2 could be: =MIN(3*B2+1.5*C2,9) |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Thanks, but that gave me total of $0.00,
"Jay" wrote: I am needing to add a SUM formula to a cell to do the following: Cell B2 is number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? D2 could be: =MIN(3*B2+1.5*C2,9) |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
If you have 3 in cell B2 and 3 in cell C2 then that formula exactly as
stated gives you 9. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "fbcmusicmark" wrote in message ... Thanks, but that gave me total of $0.00, "Jay" wrote: I am needing to add a SUM formula to a cell to do the following: Cell B2 is number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? D2 could be: =MIN(3*B2+1.5*C2,9) |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
If you look at my original example, 3 in B2 gives me $9.00 while 3 in C2
gives me $4.50 for a total of $13.50, where I need it to total $9.00. "Ken Wright" wrote: If you have 3 in cell B2 and 3 in cell C2 then that formula exactly as stated gives you 9. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "fbcmusicmark" wrote in message ... Thanks, but that gave me total of $0.00, "Jay" wrote: I am needing to add a SUM formula to a cell to do the following: Cell B2 is number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? D2 could be: =MIN(3*B2+1.5*C2,9) |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
And the formula you were given will return only 9. It gives the MINIMUM
value of either 3*B2+1.5*C2 or 9. In this case it is 9 because that is the minimum and that is what you said you needed. Regards Ken.................... "fbcmusicmark" wrote in message ... If you look at my original example, 3 in B2 gives me $9.00 while 3 in C2 gives me $4.50 for a total of $13.50, where I need it to total $9.00. "Ken Wright" wrote: If you have 3 in cell B2 and 3 in cell C2 then that formula exactly as stated gives you 9. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "fbcmusicmark" wrote in message ... Thanks, but that gave me total of $0.00, "Jay" wrote: I am needing to add a SUM formula to a cell to do the following: Cell B2 is number of adults at $3.00 each, C2 is number of children at $1.50 each and D2 will be the total of B2, C2 but I need to set a Maximum amount of $9.00, ie if B2 is 3 (3 x $3.00 = $9.00), and C2 is 3 (3 x $1.50 = $4.50) I need for D2 to be $9.00 instead of $13.50 (B2, $9.00 + C2, $4.50 = D2 $13.50). How do I format the cell so it knows the maximum amount to put in the cell is a certain amount, maybe something to do with the < sign's? D2 could be: =MIN(3*B2+1.5*C2,9) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Is there a formula in Excel to calculate a loan amount? | Excel Worksheet Functions | |||
formula for defining axis maximum and increments | Charts and Charting in Excel | |||
maximum formula length | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |