| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Long "IF" formula
I thought I would post part of the formula that you all have helped me with
just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Long "IF" formula
You can make those formulas shorter by eliminating all the =TRUE expressions
*IF* the only possible variables are either TRUE or FALSE. It looks like you could use an OR function in your last example: =IF(OR(O63,D61,E61,F61,G61,H61),"The average ","The ") -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I thought I would post part of the formula that you all have helped me with just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Long "IF" formula
I just started messing with "IF" and "TRUE" formulas yesterday. My company
had a form that was not performing properly. So I took the original formula and corrected it best I could. Im just glad I got it to work. if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. I made the form calculate the avg based on a fluctuation of the number of values give & on the values themselves. I have no idea how to use the "or" functions, like I said I just started this kind of programing yesterday. LOL "T. Valko" wrote: You can make those formulas shorter by eliminating all the =TRUE expressions *IF* the only possible variables are either TRUE or FALSE. It looks like you could use an OR function in your last example: =IF(OR(O63,D61,E61,F61,G61,H61),"The average ","The ") -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I thought I would post part of the formula that you all have helped me with just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Long "IF" formula
if I eliminate the =true statement then I have to put the
formula that made that statement true into the formula. No. From my understanding all those referenced cells already contain either TRUE or FALSE. The result of the logical test will also be either TRUE of FALSE so testing for either is redundant. For example: O63 = TRUE =IF(O63=TRUE,...... Evaluates to TRUE like this: TRUE=TRUE=TRUE You will get the same result using just: =IF(063,........ Which evaluates to TRUE like this: TRUE=TRUE -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I just started messing with "IF" and "TRUE" formulas yesterday. My company had a form that was not performing properly. So I took the original formula and corrected it best I could. Im just glad I got it to work. if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. I made the form calculate the avg based on a fluctuation of the number of values give & on the values themselves. I have no idea how to use the "or" functions, like I said I just started this kind of programing yesterday. LOL "T. Valko" wrote: You can make those formulas shorter by eliminating all the =TRUE expressions *IF* the only possible variables are either TRUE or FALSE. It looks like you could use an OR function in your last example: =IF(OR(O63,D61,E61,F61,G61,H61),"The average ","The ") -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I thought I would post part of the formula that you all have helped me with just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Long "IF" formula
P.S.
While my explanation is TRUE <g.... If it helps you follow the logic of what the formula is doing the way you have it then that's all that counts! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. No. From my understanding all those referenced cells already contain either TRUE or FALSE. The result of the logical test will also be either TRUE of FALSE so testing for either is redundant. For example: O63 = TRUE =IF(O63=TRUE,...... Evaluates to TRUE like this: TRUE=TRUE=TRUE You will get the same result using just: =IF(063,........ Which evaluates to TRUE like this: TRUE=TRUE -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I just started messing with "IF" and "TRUE" formulas yesterday. My company had a form that was not performing properly. So I took the original formula and corrected it best I could. Im just glad I got it to work. if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. I made the form calculate the avg based on a fluctuation of the number of values give & on the values themselves. I have no idea how to use the "or" functions, like I said I just started this kind of programing yesterday. LOL "T. Valko" wrote: You can make those formulas shorter by eliminating all the =TRUE expressions *IF* the only possible variables are either TRUE or FALSE. It looks like you could use an OR function in your last example: =IF(OR(O63,D61,E61,F61,G61,H61),"The average ","The ") -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I thought I would post part of the formula that you all have helped me with just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Long "IF" formula
My mistake, I dont think they were "TRUE" formulas. the formula refers back
to the "AND" formulas. So if the AND formula was true then ......., if false then it would go to the next "AND" formula and so on. "T. Valko" wrote: if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. No. From my understanding all those referenced cells already contain either TRUE or FALSE. The result of the logical test will also be either TRUE of FALSE so testing for either is redundant. For example: O63 = TRUE =IF(O63=TRUE,...... Evaluates to TRUE like this: TRUE=TRUE=TRUE You will get the same result using just: =IF(063,........ Which evaluates to TRUE like this: TRUE=TRUE -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I just started messing with "IF" and "TRUE" formulas yesterday. My company had a form that was not performing properly. So I took the original formula and corrected it best I could. Im just glad I got it to work. if I eliminate the =true statement then I have to put the formula that made that statement true into the formula. I made the form calculate the avg based on a fluctuation of the number of values give & on the values themselves. I have no idea how to use the "or" functions, like I said I just started this kind of programing yesterday. LOL "T. Valko" wrote: You can make those formulas shorter by eliminating all the =TRUE expressions *IF* the only possible variables are either TRUE or FALSE. It looks like you could use an OR function in your last example: =IF(OR(O63,D61,E61,F61,G61,H61),"The average ","The ") -- Biff Microsoft Excel MVP "NotExcelingNow" wrote in message ... I thought I would post part of the formula that you all have helped me with just to say thank you. I got everything running now. There were 7 formulas like this one plus 2 data tables one of which had 12 "TRUE" formulas =ROUND(IF(O63=TRUE,AVERAGE(P45:Q46),IF(D61=TRUE,AV ERAGE(P44:Q46),IF(E61=TRUE,AVERAGE(P43:Q46),IF(F61 =TRUE,AVERAGE(P42:Q46),IF(G61=TRUE,AVERAGE(P41:Q46 ),IF(H61=TRUE,AVERAGE(P40:Q46),P46)))))),0) =AND(M63=TRUE,H46=H45,H45=H44,H44=H43,H43=H42,H41= H42,H41=H40) =IF(O63=TRUE,"The average ",IF(D61=TRUE,"The average ",IF(E61=TRUE,"The average ",IF(F61=TRUE,"The average ",IF(G61=TRUE,"The average ",IF(H61=TRUE,"The average ","The ")))))) I got everything running just like I wanted, thank you sooo much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| While using find and replace i am getting "formula too long" | Excel Discussion (Misc queries) | |||
| "formula is too long" AND test for whether double-quotes are next-to text or number?? | Excel Discussion (Misc queries) | |||
| "Formula too long" when changing linked sources?? | Excel Discussion (Misc queries) | |||
| Error message "formula is too long" | Excel Discussion (Misc queries) | |||
| Error message "formula is too long" | Excel Worksheet Functions | |||
Linear Mode
