| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT
IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
i maybe confused but try one way...
on B4 = MIN(400,A2)*5 -- ***** birds of the same feather flock together.. "Zigball" wrote: HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Zigball,
Please don't post in all capitals - it is considered to be shouting and is harder to read especially if English in not the person's first language. Now on to your problem: Try: =Max(S2,400)*5 ot if you want to display a blank cell until there is an entry in A2 try: =IF(A2="","",MAX(A2,400)*5) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Zigball" wrote in message ups.com... HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Thank you Driller, thanks
driller wrote: i maybe confused but try one way... on B4 = MIN(400,A2)*5 -- ***** birds of the same feather flock together.. "Zigball" wrote: HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Thank you Driller, thanks
driller wrote: i maybe confused but try one way... on B4 = MIN(400,A2)*5 -- ***** birds of the same feather flock together.. "Zigball" wrote: HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
YOU'RE WELCOME!!!
-- ***** birds of the same feather flock together.. "Zigball" wrote: Thank you Driller, thanks driller wrote: i maybe confused but try one way... on B4 = MIN(400,A2)*5 -- ***** birds of the same feather flock together.. "Zigball" wrote: HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Thank alot Don, thanks I wasn't shouting my cpu was locked on caps and
I didn't change it, sorry no offense! Don Guillett wrote: PLEASE do not type in all capitals. It is not considered to be good netiquette. It is SHOUTING! Have a look in the help index for MAX and post back if you still have questions. -- Don Guillett SalesAid Software "Zigball" wrote in message ups.com... HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
That's OK., I should have said MIN instead of MAX but the help index would
have referred you to the other anyway. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... PLEASE do not type in all capitals. It is not considered to be good netiquette. It is SHOUTING! Have a look in the help index for MAX and post back if you still have questions. -- Don Guillett SalesAid Software "Zigball" wrote in message ups.com... HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Yes MAX(), MIN(), very confusing for both of us isn't it? <g
-- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Don Guillett" wrote in message ... That's OK., I should have said MIN instead of MAX but the help index would have referred you to the other anyway. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... PLEASE do not type in all capitals. It is not considered to be good netiquette. It is SHOUTING! Have a look in the help index for MAX and post back if you still have questions. -- Don Guillett SalesAid Software "Zigball" wrote in message ups.com... HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FORMULA THAT LIMITS WHAT IT CALCULATES VS WHATS ACTUAL
Hell, At my age, I'm easily confused about lots of stuff.
-- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... Yes MAX(), MIN(), very confusing for both of us isn't it? <g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Don Guillett" wrote in message ... That's OK., I should have said MIN instead of MAX but the help index would have referred you to the other anyway. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... PLEASE do not type in all capitals. It is not considered to be good netiquette. It is SHOUTING! Have a look in the help index for MAX and post back if you still have questions. -- Don Guillett SalesAid Software "Zigball" wrote in message ups.com... HELLO GOOGLE GROUPS I HAVE A PROBLEM, I NEED TO MAKE A SPREADHEET THAT IS BEYOND MY CAPABILITIES SO I AM ASKING FOR YOUR HELP. THIS FORMULA THAT I NEED TO ME SEEMS COMPLEX BUT TO SOMEONE THATS OK WITH IF STATEMENTS OR WHATEVER, MIGHT NOT HAVE A PROBLEM. OK, I WOULD LIKE TO ACHIEVE A FORMULA THAT CALCULATES A NUMBER IN A CELL FOR INSTANCE CELL A2 (THE ROOT NUMBER). FOR EXAMPLE I TYPE 500 INTO CELL A2 THEN IN CELL B4 IS WHERE THE FORMULA I NEED TO FIND WILL LIVE. IN CELL B4 I WANT A FORMULA THAT WILL MULTIPLY THE NUMBER IN A2 BY LETS SAY 5 (=A2*5) I KNOW THAT MUCH. THE CATCH IS THAT I DO NOT WANT CELL B4 TO MULTIPLY ANY AMOUNTS PASS 400 THAT IS IN CELL A2. SO IN CELL B4 I WANT IT TO BE LIMITED TO MULTIPLYING ANY VALUES THAT GO PASSES 400. ALTHOUGH 500 IS IN CELL A2 AND FORMULA STATES THAT I WANT A2 TO BE MULTIPLIED BY 5, A2 IS 500 I ONLY WANT TO MULTIPLY 400 SO THE ANSWER SHOULD BE 2000 NOT 2500. ANOTHER CATCH IS THAT I CAN NOT CHANGE THE ACTUAL NUMBER IN A2 WHICH IS FIVE HUNDRED IT MUST STAY THE SAME, IT IS REFERENCED BY OTHER CELLS THAT ACTUALLY NEED THE ACTUAL NUMBER THATS THERE. EXAMPLE =IF CELL A2 IS OVER OR EQUAL TO 400 THEN CELL A2 VALUE EQUALS 400 THEN MULTIPLY BY 5 IF CELL A2 IS LESS THAN 400 THEN MULTIPLY CELL A2 ACTUAL VALUE BY 5 HOPEFULLY THIS IS OK TO UNDERSTAND THANKS IN ADVANCE! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Excel formula that calculates percentage of increase or decrease | Excel Worksheet Functions | |||
| Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Linear Mode
