Complex decisions, maybe?
hey guys,
got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
Do you mean
=IF(ABS(A1+B1-C10)5,"A",A1+B1) -- __________________________________ HTH Bob "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
If I read your question correctly, I believe you are trying to put two
different values in C1. You indicated that C1 has a value and you want to compare that value against C10 and if a certain criteria exist, then the value of C1 should output "A". Unless there is something more complex than I am aware of, you cannot do that. However, if you compare a formula against the value of C10, then you can output "A" to C1, ie =IF((A1-B1)=C10,"TRUE","FALSE), where the (A1-B1) represents the value you want in C1 to compare to C10. Now to answer your question about the formula. I am going to change up a little and say that you do have a value of 2 in C1 and the following formula is in D1: =IF(OR(C10-C1=5, C10-C1<=-5),"A","") NOTE there is a 5 and -5 in the formula. You did not indicate what to do if the comparison does not meet the criteria for an "A" output, so I made it null via the double quotes. Now if you want the output of "A" to be C1, you will need to replace both instances of C1 in my formula with the formula that calulates to give you the answer 2 (in your example). Place this formula in C1. Example (C1): =IF(OR(C10-(B1-A1)=5,C10-(B1-A1)<=-5),"A","") Hope that works for you. Les "Noob McKnownowt" wrote: hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
I think you may be looking for this...
=IF(ABS(C1-C10)=5,"A","") where, because you didn't say what to do if the difference was something else, I output nothing if the difference is not 5. -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
Thanks for the help Bob, but not quite what i am after,
if C1 = 5 and C10 = 10 or 0 then C1 is within 5 (more than or less than) of that number so return the letter A there is more that will need to go into the formula so it doesnt need to return a false value at the min. but the help is very much appreciated. The Noob. "Bob Phillips" wrote: Do you mean =IF(ABS(A1+B1-C10)5,"A",A1+B1) -- __________________________________ HTH Bob "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
once again guys thanks for the posts,
you have the right idea, but i dont think i am explaining myself properly. i need the letter returning if the result of a calculation falls within a certain range, but because these numbers are dynamic its difficult to explain. but yes i am essentially trying to compare one number against another number and then base a decision on the difference between them. say i have the number 5 (C10) the number i am comparing can be anything from 1 to 10 (C1) and return the letter 'A'. but it is important that the letter 'A' is only returned if the number being compared falls equally or within 5 either side of the 'base' number (C10) i.e. any number between 1 and 10 will return 'A' 0 or 11 or any numbers beyond that boundry will return something else. thanks guys, The Noob. "Rick Rothstein" wrote: I think you may be looking for this... =IF(ABS(C1-C10)=5,"A","") where, because you didn't say what to do if the difference was something else, I output nothing if the difference is not 5. -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
How about this then...
=IF(ABS(A1-5.5)<=5,"A","") The 5.5 comes from averaging the two end value (1 and 10) that need to be less than the midpoint value (the 5 that the absolute value is less than). -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... once again guys thanks for the posts, you have the right idea, but i dont think i am explaining myself properly. i need the letter returning if the result of a calculation falls within a certain range, but because these numbers are dynamic its difficult to explain. but yes i am essentially trying to compare one number against another number and then base a decision on the difference between them. say i have the number 5 (C10) the number i am comparing can be anything from 1 to 10 (C1) and return the letter 'A'. but it is important that the letter 'A' is only returned if the number being compared falls equally or within 5 either side of the 'base' number (C10) i.e. any number between 1 and 10 will return 'A' 0 or 11 or any numbers beyond that boundry will return something else. thanks guys, The Noob. "Rick Rothstein" wrote: I think you may be looking for this... =IF(ABS(C1-C10)=5,"A","") where, because you didn't say what to do if the difference was something else, I output nothing if the difference is not 5. -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
=IF(ABS(C1-C10)<5,"A","") or =IF(ABS(C1-C10)<=5,"A","")
Not clear why 5 above is acceptable but 5 below isn't. If you really do want that, you could have =IF(AND(C1-C10<=5,C10-C1<5),"A","") -- David Biddulph "Noob McKnownowt" wrote in message ... once again guys thanks for the posts, you have the right idea, but i dont think i am explaining myself properly. i need the letter returning if the result of a calculation falls within a certain range, but because these numbers are dynamic its difficult to explain. but yes i am essentially trying to compare one number against another number and then base a decision on the difference between them. say i have the number 5 (C10) the number i am comparing can be anything from 1 to 10 (C1) and return the letter 'A'. but it is important that the letter 'A' is only returned if the number being compared falls equally or within 5 either side of the 'base' number (C10) i.e. any number between 1 and 10 will return 'A' 0 or 11 or any numbers beyond that boundry will return something else. thanks guys, The Noob. "Rick Rothstein" wrote: I think you may be looking for this... =IF(ABS(C1-C10)=5,"A","") where, because you didn't say what to do if the difference was something else, I output nothing if the difference is not 5. -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
Complex decisions, maybe?
You gentlemen are saints, thank you very much for the help, very much
apprieciated. The Noob. "David Biddulph" wrote: =IF(ABS(C1-C10)<5,"A","") or =IF(ABS(C1-C10)<=5,"A","") Not clear why 5 above is acceptable but 5 below isn't. If you really do want that, you could have =IF(AND(C1-C10<=5,C10-C1<5),"A","") -- David Biddulph "Noob McKnownowt" wrote in message ... once again guys thanks for the posts, you have the right idea, but i dont think i am explaining myself properly. i need the letter returning if the result of a calculation falls within a certain range, but because these numbers are dynamic its difficult to explain. but yes i am essentially trying to compare one number against another number and then base a decision on the difference between them. say i have the number 5 (C10) the number i am comparing can be anything from 1 to 10 (C1) and return the letter 'A'. but it is important that the letter 'A' is only returned if the number being compared falls equally or within 5 either side of the 'base' number (C10) i.e. any number between 1 and 10 will return 'A' 0 or 11 or any numbers beyond that boundry will return something else. thanks guys, The Noob. "Rick Rothstein" wrote: I think you may be looking for this... =IF(ABS(C1-C10)=5,"A","") where, because you didn't say what to do if the difference was something else, I output nothing if the difference is not 5. -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... hey guys, got a quick question for any one able to answer :) say i have a table with various SUM functions from C1 to C10 calculating the numbers in A1 and B1 down to A10 and B10 in cell C1 i have 2 and in C10 i have 7 if C1 is 5 more than or 5 less than the value of C10 then the value of that cell should have an "A" out put to it. there are other conditions but hopefully if you can help me with this i can figure the rest out. thanks for any assistance given. The Noob. |
All times are GMT +1. The time now is 12:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com