Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default between function?

I am trying to write an if function to find out if a value falls between two
numbers

=if(AM7(is between 0.005 and 0.999),"etc","etc")

Is there a function I can use to make this happen?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default between function?

Hi,
you can use

=if(and(AM7+0.005,AM7<=0.999),etc,etc)

"klh84" wrote:

I am trying to write an if function to find out if a value falls between two
numbers

=if(AM7(is between 0.005 and 0.999),"etc","etc")

Is there a function I can use to make this happen?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default between function?

"klh84" wrote:
=if(AM7(is between 0.005 and 0.999),"etc","etc")


Ostensibly, you might want:

=if(and(0.005<=AM7,AM7<=0.999),"etc1","etc2")

But beware of such comparisons. Most decimal fractions, and those in
particular, cannot be represented exactly. Moreover, if AM7 is formatted as
Number with 3 decimal places, a number like 0.9991 might __appear__ to be
0.999, but it will fail the "between" test.

It would be better to write:

=if(and(0.005<=round(AM7,3),round(AM7,3)<=0.999), "etc1","etc2")

Even better: if AM73 can be =round(formula,3), then the first comparison
will work as intended.


----- original message -----

"klh84" wrote:
I am trying to write an if function to find out if a value falls between two
numbers

=if(AM7(is between 0.005 and 0.999),"etc","etc")

Is there a function I can use to make this happen?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default between function?

Use the AND function:

=IF(AND(AM7=0.005,AM7<=0.999),"etc","etc")

--
Biff
Microsoft Excel MVP


"klh84" wrote in message
...
I am trying to write an if function to find out if a value falls between
two
numbers

=if(AM7(is between 0.005 and 0.999),"etc","etc")

Is there a function I can use to make this happen?



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
Excel Data Validation/Lookup function does function correcty Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 10:09 PM.

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

About Us

"It's about Microsoft Excel"