|
arguments
i am trying to get a formula to increase the dollar amount per $100.00 and
have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
The first IF test that evaluates to TRUE causes the associated 'THEN' clause
to be returned. So if the <=600 test fails, we can be sure that the =600.01 test passes, so the result would be C3+8. If you have a LOT (7) different ranges, you'll wind up switching to a vlookup function rather than nested IFs. Otherwise, arrange your tests differently: =if(c3=800.01,c3+20,if(c3=700.01,c3+12,if(c3=60 0.01,c3+8,c3+6))). "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
Because the =600 condition has already been met............reverse the
order of your conditions, start with 800, then 700 etc.......working your way down instead of up.......... Vaya con Dios, Chuck, CABGx3 "gemini0662" wrote in message ... i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20) ))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
If you want to use if statements, you have to reorganize it. Once a true
statement is found, the rest of the formula is not processed. Anything above 600.01 will return 8 because this part IF(C3=600.01,C3+8 will always be true. =C3+IF(C3=800.01,20,IF(C3=700.01,12,IF(C3=600.0 1,8,6))) However, since Excel has a limit of 7 nested functions, I generally avoid using IF statements like this. You could try the following, which uses a lookup (check excel help for VLOOKUP and the link below - post back if you still have questions) http://www.contextures.com/xlFunctions02.html =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20}, 2,1) Instead of putting the lookup table directly in the formula you could create the table somewhere in your workbook. and just reference it in the VLOOKUP formula. So if the lookup table were in cells A1:B4 A B 0 6 600.01 8 700.01 12 800.01 20 the formula becomes =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples in excel help. "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
Excuse my clock!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... The syntax of your formula is OK, it's the logic that's lacking. Let's take your second argument, IF(C3=600.01 NOW, doesn't that equate to "True" when C3 contains a value of say, 1000? That being the case, the formula calculates, C3+8 AND then *stops* calculating, since a true condition has been met. It (the formula) never even reaches the other arguments to evaluate them. What you have to do to 'fix' your formula is reverse the arguments. Put the larger values at the front of the formula so that the evaluation does not skip over the lesser values. Also, take *all* possibilities into consideration, if necessary! Is it important that if C3 is 0 or negative (<0), or even empty, it will return the first calculation of C3+6 ? Is that what you really want to happen (maybe you do)? If C3 has not yet even been keyed in a value, your formula cell will still display a 6. Does this help? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gemini0662" wrote in message ... i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
The syntax of your formula is OK, it's the logic that's lacking.
Let's take your second argument, IF(C3=600.01 NOW, doesn't that equate to "True" when C3 contains a value of say, 1000? That being the case, the formula calculates, C3+8 AND then *stops* calculating, since a true condition has been met. It (the formula) never even reaches the other arguments to evaluate them. What you have to do to 'fix' your formula is reverse the arguments. Put the larger values at the front of the formula so that the evaluation does not skip over the lesser values. Also, take *all* possibilities into consideration, if necessary! Is it important that if C3 is 0 or negative (<0), or even empty, it will return the first calculation of C3+6 ? Is that what you really want to happen (maybe you do)? If C3 has not yet even been keyed in a value, your formula cell will still display a 6. Does this help? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gemini0662" wrote in message ... i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
thanks, this helped a lot...now...how do i get it to hide the #NA if the
value is 0? marcia "JMB" wrote: If you want to use if statements, you have to reorganize it. Once a true statement is found, the rest of the formula is not processed. Anything above 600.01 will return 8 because this part IF(C3=600.01,C3+8 will always be true. =C3+IF(C3=800.01,20,IF(C3=700.01,12,IF(C3=600.0 1,8,6))) However, since Excel has a limit of 7 nested functions, I generally avoid using IF statements like this. You could try the following, which uses a lookup (check excel help for VLOOKUP and the link below - post back if you still have questions) http://www.contextures.com/xlFunctions02.html =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20}, 2,1) Instead of putting the lookup table directly in the formula you could create the table somewhere in your workbook. and just reference it in the VLOOKUP formula. So if the lookup table were in cells A1:B4 A B 0 6 600.01 8 700.01 12 800.01 20 the formula becomes =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples in excel help. "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
If the value is zero the formulae return 6 on my machine, same as your
original formula. Can the value in C3 be negative? You can test for #n/a with =IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........)) but the formula will be longer than the nested IF's. If you don't know what the lower end of the range can be for C3 and there are only 4 conditions, consider using the nested IF's. I brought up VLOOKUP because it is usually a good solution when you start adding more levels. "gemini0662" wrote: thanks, this helped a lot...now...how do i get it to hide the #NA if the value is 0? marcia "JMB" wrote: If you want to use if statements, you have to reorganize it. Once a true statement is found, the rest of the formula is not processed. Anything above 600.01 will return 8 because this part IF(C3=600.01,C3+8 will always be true. =C3+IF(C3=800.01,20,IF(C3=700.01,12,IF(C3=600.0 1,8,6))) However, since Excel has a limit of 7 nested functions, I generally avoid using IF statements like this. You could try the following, which uses a lookup (check excel help for VLOOKUP and the link below - post back if you still have questions) http://www.contextures.com/xlFunctions02.html =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20}, 2,1) Instead of putting the lookup table directly in the formula you could create the table somewhere in your workbook. and just reference it in the VLOOKUP formula. So if the lookup table were in cells A1:B4 A B 0 6 600.01 8 700.01 12 800.01 20 the formula becomes =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples in excel help. "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
well, i did as you suggested using the vlook up you listed. i put the values
on a seperate page in the work book in case the dollar value would ever change. at one point and time i was using 500 lines for one formula. i just gave the worksheet a new line with 0 as a default and it works well enough. the value can never be negative i don't think since it is a fee schedule. i am not sure i would know how to run the formula to test it. i will try and test it some other time when i have nothing else to do. thanks for all your help. i learned a lot. "JMB" wrote: If the value is zero the formulae return 6 on my machine, same as your original formula. Can the value in C3 be negative? You can test for #n/a with =IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........)) but the formula will be longer than the nested IF's. If you don't know what the lower end of the range can be for C3 and there are only 4 conditions, consider using the nested IF's. I brought up VLOOKUP because it is usually a good solution when you start adding more levels. "gemini0662" wrote: thanks, this helped a lot...now...how do i get it to hide the #NA if the value is 0? marcia "JMB" wrote: If you want to use if statements, you have to reorganize it. Once a true statement is found, the rest of the formula is not processed. Anything above 600.01 will return 8 because this part IF(C3=600.01,C3+8 will always be true. =C3+IF(C3=800.01,20,IF(C3=700.01,12,IF(C3=600.0 1,8,6))) However, since Excel has a limit of 7 nested functions, I generally avoid using IF statements like this. You could try the following, which uses a lookup (check excel help for VLOOKUP and the link below - post back if you still have questions) http://www.contextures.com/xlFunctions02.html =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20}, 2,1) Instead of putting the lookup table directly in the formula you could create the table somewhere in your workbook. and just reference it in the VLOOKUP formula. So if the lookup table were in cells A1:B4 A B 0 6 600.01 8 700.01 12 800.01 20 the formula becomes =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples in excel help. "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
arguments
The 0 is to capture anything below 600.01. The VLOOKUP is looking for the
biggest number that is less than/equal to C3. So without the zero you'll get the #N/A. You can change the table to return a zero if C3 = 0 and 6 for anything between 0.01 and 600 (inclusive). A B 0 0 0.01 6 600.01 8 700.01 12 800.01 20 As you noticed one benefit is it is easier to change the schedule (particularly if you have more than just a few cells referencing the table). "gemini0662" wrote: well, i did as you suggested using the vlook up you listed. i put the values on a seperate page in the work book in case the dollar value would ever change. at one point and time i was using 500 lines for one formula. i just gave the worksheet a new line with 0 as a default and it works well enough. the value can never be negative i don't think since it is a fee schedule. i am not sure i would know how to run the formula to test it. i will try and test it some other time when i have nothing else to do. thanks for all your help. i learned a lot. "JMB" wrote: If the value is zero the formulae return 6 on my machine, same as your original formula. Can the value in C3 be negative? You can test for #n/a with =IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........)) but the formula will be longer than the nested IF's. If you don't know what the lower end of the range can be for C3 and there are only 4 conditions, consider using the nested IF's. I brought up VLOOKUP because it is usually a good solution when you start adding more levels. "gemini0662" wrote: thanks, this helped a lot...now...how do i get it to hide the #NA if the value is 0? marcia "JMB" wrote: If you want to use if statements, you have to reorganize it. Once a true statement is found, the rest of the formula is not processed. Anything above 600.01 will return 8 because this part IF(C3=600.01,C3+8 will always be true. =C3+IF(C3=800.01,20,IF(C3=700.01,12,IF(C3=600.0 1,8,6))) However, since Excel has a limit of 7 nested functions, I generally avoid using IF statements like this. You could try the following, which uses a lookup (check excel help for VLOOKUP and the link below - post back if you still have questions) http://www.contextures.com/xlFunctions02.html =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20}, 2,1) Instead of putting the lookup table directly in the formula you could create the table somewhere in your workbook. and just reference it in the VLOOKUP formula. So if the lookup table were in cells A1:B4 A B 0 6 600.01 8 700.01 12 800.01 20 the formula becomes =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples in excel help. "gemini0662" wrote: i am trying to get a formula to increase the dollar amount per $100.00 and have been able to get the first part. i am not sure how to get the $$$ to increase since there are a number of arguments per category. =IF(C3<=600,C3+6,IF(C3=600.01,C3+8,IF(C3=700.01, C3=12,IF(C3=800.01,C3+20)))) this formula will not recalculate over 700.00 and up...any suggestions? clear explanations would be appreciated since i am new to the formula world. the last formula i came up withfor a different project was pieced together from several different spreadsheets. |
| All times are GMT +1. The time now is 03:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com