Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 16th 08, 03:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 37
Default 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.

  #2   Report Post  
Old November 16th 08, 04:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 2,420
Default 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.



  #3   Report Post  
Old November 16th 08, 04:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 470
Default 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.

  #4   Report Post  
Old November 16th 08, 04:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default 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.


  #5   Report Post  
Old November 16th 08, 04:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 37
Default 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.






  #6   Report Post  
Old November 16th 08, 04:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 37
Default 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.



  #7   Report Post  
Old November 16th 08, 05:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default 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 news
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.




  #8   Report Post  
Old November 16th 08, 05:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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 news
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.





  #9   Report Post  
Old November 16th 08, 05:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 37
Default 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 news
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.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bit of a complex SUM Risky Dave Excel Worksheet Functions 4 March 18th 08 03:30 PM
Complex sum Greshter Excel Discussion (Misc queries) 1 March 9th 07 04:34 AM
Complex IF Anita Excel Worksheet Functions 5 September 5th 06 11:35 AM
Complex Index Match Help (or at least complex to me) Jennifer Reitman Excel Discussion (Misc queries) 3 August 10th 06 08:51 PM
Complex sum Greshter Excel Discussion (Misc queries) 4 July 21st 06 11:41 PM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017