| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Desired Results
I need to set a maximum amount on several input values in a formula.
=(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Desired Results
What value is in H11? That's your IF statement where E11C11*5 (Also known as
C11 less than E11/5) "Wavmaster" wrote: I need to set a maximum amount on several input values in a formula. =(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Desired Results
H11 reflects the allowed useable value for C11*5 at a given time. it is
always a multiple of 5. It relates to E11 in as much as E11 can be any number, H11 can be multiples of 5 up to 500. Formula is =(MIN(C11,100)*5)+IF(C11*5500,500-500) "Sean Timmons" wrote: What value is in H11? That's your IF statement where E11C11*5 (Also known as C11 less than E11/5) "Wavmaster" wrote: I need to set a maximum amount on several input values in a formula. =(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Desired Results
H11 reflects the allowed useable value for C11*5 at a given time. it is
always a multiple of 5. It relates to E11 in as much as E11 can be any number, H11 can be multiples of 5 up to 500. Formula is =(MIN(C11,100)*5)+IF(C11*5500,500-500) "Sean Timmons" wrote: What value is in H11? That's your IF statement where E11C11*5 (Also known as C11 less than E11/5) "Wavmaster" wrote: I need to set a maximum amount on several input values in a formula. =(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Desired Results
so, in the example you describe at bottom, C11 is greater than 100, thus the
formula here will return 500. Not sure why the 500-500, as this is the same as just putting 0 in there... Not sure about MIN(1)? That can just be 1... Are you saying you merely want the result of the formula to max out at 500+F15+G15? =MIN(500+F15+G15,(1+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500))) "wavmaster" wrote: H11 reflects the allowed useable value for C11*5 at a given time. it is always a multiple of 5. It relates to E11 in as much as E11 can be any number, H11 can be multiples of 5 up to 500. Formula is =(MIN(C11,100)*5)+IF(C11*5500,500-500) "Sean Timmons" wrote: What value is in H11? That's your IF statement where E11C11*5 (Also known as C11 less than E11/5) "Wavmaster" wrote: I need to set a maximum amount on several input values in a formula. =(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Desired Results
That works when C11 is greater than 100 but not when C11 is less than 100
"Sean Timmons" wrote: so, in the example you describe at bottom, C11 is greater than 100, thus the formula here will return 500. Not sure why the 500-500, as this is the same as just putting 0 in there... Not sure about MIN(1)? That can just be 1... Are you saying you merely want the result of the formula to max out at 500+F15+G15? =MIN(500+F15+G15,(1+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500))) "wavmaster" wrote: H11 reflects the allowed useable value for C11*5 at a given time. it is always a multiple of 5. It relates to E11 in as much as E11 can be any number, H11 can be multiples of 5 up to 500. Formula is =(MIN(C11,100)*5)+IF(C11*5500,500-500) "Sean Timmons" wrote: What value is in H11? That's your IF statement where E11C11*5 (Also known as C11 less than E11/5) "Wavmaster" wrote: I need to set a maximum amount on several input values in a formula. =(MIN(1))+IF(E11C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5=H11,H11+F15+G15-500) This formula suits my purpose until the value of C11 exceeds the value of E11/5 after C11 exceeds 100. The Maximum value for the target cell is 500 plus F15 plus G15 example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510 (this works when C11= any whole number from 101 up unless it is greater than E11/5) When the value of C11= 497 or greater and the value of E11, F15 and G15 remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15. Once the value of E11 exceeds 5 x C11 the result returns to the desired result How can I elimiate this occurrance when C11 exceeds E11/5? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how should I setup workbook to acheive my desired results? | Excel Discussion (Misc queries) | |||
| highlighting cells that have desired results | Excel Discussion (Misc queries) | |||
| Returning the desired value if multiple values exist???? | Excel Worksheet Functions | |||
| Results of conditional formatting not what desired. How do I corr | Excel Worksheet Functions | |||
| Vlookup Returning Same Results on Each Row | Excel Discussion (Misc queries) | |||
Linear Mode
