Function Argument Question

I'm having a brain freeze and cannot come up with the formula to express the 
following:

If A1 is greater than 0 but less than 3, return 50,

If A1 is greater than 3 but less than 6, return 100,

If A1 is great than 6, return 200.

I was looking for a "between" function like in formatting, but I don't think 
that is how it's done.

Thanks in advance for any help!
0
Utf
4/26/2010 3:28:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
376 Views

Similar Articles

[PageSpeed] 53

Hi War,

Try this:

=if(A1>=6,200,if(A1>3,200,50))


-- 
Hope this helps


"WAR" wrote:

> I'm having a brain freeze and cannot come up with the formula to express the 
> following:
> 
> If A1 is greater than 0 but less than 3, return 50,
> 
> If A1 is greater than 3 but less than 6, return 100,
> 
> If A1 is great than 6, return 200.
> 
> I was looking for a "between" function like in formatting, but I don't think 
> that is how it's done.
> 
> Thanks in advance for any help!
0
Utf
4/26/2010 3:32:01 PM
Thanks, Judith!

I think you typed in an error though, should it say "A1>="?  I took out the 
"=" and it worked.

Wayne

"JudithJubilee" wrote:

> Hi War,
> 
> Try this:
> 
> =if(A1>=6,200,if(A1>3,200,50))
> 
> 
> -- 
> Hope this helps
> 
> 
> "WAR" wrote:
> 
> > I'm having a brain freeze and cannot come up with the formula to express the 
> > following:
> > 
> > If A1 is greater than 0 but less than 3, return 50,
> > 
> > If A1 is greater than 3 but less than 6, return 100,
> > 
> > If A1 is great than 6, return 200.
> > 
> > I was looking for a "between" function like in formatting, but I don't think 
> > that is how it's done.
> > 
> > Thanks in advance for any help!
0
Utf
4/26/2010 4:46:13 PM
Hi.  Just to mention...

 > If A1 is greater than 0 but less than 3, return 50,

A1 of zero, or -2 also return 50.
A1 <=0 was not specified.

Just thought I'd mention it.  :>)
= = = = = =
HTH  :>)
Dana DeLouis

On 4/26/10 12:46 PM, WAR wrote:
> Thanks, Judith!
>
> I think you typed in an error though, should it say "A1>="?  I took out the
> "=" and it worked.
>
> Wayne
>
> "JudithJubilee" wrote:
>
>> Hi War,
>>
>> Try this:
>>
>> =if(A1>=6,200,if(A1>3,200,50))
>>
>>
>> --
>> Hope this helps
>>
>>
>> "WAR" wrote:
>>
>>> I'm having a brain freeze and cannot come up with the formula to express the
>>> following:
>>>
>>> If A1 is greater than 0 but less than 3, return 50,
>>>
>>> If A1 is greater than 3 but less than 6, return 100,
>>>
>>> If A1 is great than 6, return 200.
>>>
>>> I was looking for a "between" function like in formatting, but I don't think
>>> that is how it's done.
>>>
>>> Thanks in advance for any help!
0
Dana
4/26/2010 4:53:42 PM
You did not specify what  to do if the answer was exactly 6. Less than 6 = 
100. Greater than 6 = 200. Judith did not make a mistake.
-- 
HTH...

Jim Thomlinson


"WAR" wrote:

> Thanks, Judith!
> 
> I think you typed in an error though, should it say "A1>="?  I took out the 
> "=" and it worked.
> 
> Wayne
> 
> "JudithJubilee" wrote:
> 
> > Hi War,
> > 
> > Try this:
> > 
> > =if(A1>=6,200,if(A1>3,200,50))
> > 
> > 
> > -- 
> > Hope this helps
> > 
> > 
> > "WAR" wrote:
> > 
> > > I'm having a brain freeze and cannot come up with the formula to express the 
> > > following:
> > > 
> > > If A1 is greater than 0 but less than 3, return 50,
> > > 
> > > If A1 is greater than 3 but less than 6, return 100,
> > > 
> > > If A1 is great than 6, return 200.
> > > 
> > > I was looking for a "between" function like in formatting, but I don't think 
> > > that is how it's done.
> > > 
> > > Thanks in advance for any help!
0
Utf
4/26/2010 5:04:05 PM
Reply:

Similar Artilces:

Pivot Table Question #12
I've created a pivot table and it works well - counts the number of items in each category. What it doesn't do is count the number of blank cells. It gives me a blank category but, alas, it is blank. I want the blank category line to tell me how many blank cells I have. I know that this is possible, but just can't seem to make it work. Can someone help? Thanks! To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Betty Csehi wrote: > I&#...

General question re Error Handling
hello experts This is just a general question for my own education. I've dabbled in a fair amount of Access VBA in the past, but have just started learning about Excel VBA. In Access, we would use Error Handling in almost every sub, but In Excel, error Handling seems to be the exception rather than the rule. I can't even find much about it in the Excel VBA help, and there are relatively few references to it in this forum. What gives? Just Curious Terry W. Terry, I use it all the time. My favorite approach is: Public Const bDebug as boolean = true or false (de...

Index & Matching Functions
I have created a workbook with three sheets. The first contains data pertaining to a project type and a specific ID number that I have assigned to it. For Example: 1 Areospace Ground Equipment Complexes 2 Hangers 3 Maintenance Facilities 4 Warehouse Etc..... I have another sheet that contains a database of all my firm's completed projects in which I detail several fields (i.e. project number, description, location, cost, client, and its Project Type ID#-taken from the list aforemention...

SMTP - ISP Question
Hi everyone, please accept my apologies in advance if this slightly off-topic but I though this would be a good place to ask... I am looking for some recommendations for a UK ISP who will register a domain (web hosting not terribly important) and will provide me with an SMTP feed. I have the connection and the public IP addresses already so I do not need a bundled service. Many thanks to all in advance, Kapa ...

a Solver question
Hi, I am trying to use Solver to solve a scheduling problem. We get orders every Sunday, and then schedule the production for the following week. The order is as follows: Prod1 Prod2 Prod3 M 4 1 T 1 W TR 2 1 F 1 SA S 2 The numbers indicate the amount of products we need produced BY this day. And we have schedule tables for two plants like this: Plant 1: Prod1 Prod2 Prod3 M x11 x12 T x21 x22 W x31 TR .... ... ...

Mo2004
Recently ( a few months ago) I started using MS Money 2004. Previously I had been managing finances on excel with formulas I had created. This was wroking great but someone suggested to switch to financial software to make things flow a bit easier. Since using Money 2004 I have had some struggles: #1 - My budget varies from month to month (who's doesn't?). Whenever I make changes to my budget it apears to retroactivly change all previous months. How can I get it to just change any given month? Along the same lines, can I make adjustments to prior months? #2 - At the end of...

Compatiibility question
Hello Since I am going to college I qualify for the academic version of Publisher 2003. If I buy the academic version is this completely compatable with Publisher 2003? I anticipate sharing files with friends who have Publisher 2003. One other short question is when is a new version of Publisher coming out. Thanks Paul I was not aware of an Academic priced version of Publisher 2003. However, there is only one version available, so both will be 100% compatible. The next version of Publisher is due out October/November of 2006. -- Brian Kvalheim Microsoft Publisher MVP http://www....

simple checkbox question
I really dont know much about VBA and/or macros and have what probably is an extremly basic question. I wasnt sure how to word it so when trying to search it i didnt really get any answers. all I want to do is add several checkboxes to a spreadsheet, if the user checks the box, I want the cell underneath it to be equal to 1, or any number or letter, it doesnt matter. i just used 1 in an IF formula but can change it if needed. what i am doing is a timesheet so that users can keep track of how many hours they worked each day, and if that day is a holiday, i want them to be able to use the ch...

Why doesn't what I enter in the function bar appear in the cell?
I have a workbook that contains several copies of a master template worksheet. On one of these sheets when I enter text, it appears correctly in the function bar, but appears as a string of &s in the cell. This has only just occurred, and as far as I know used to work OK, i.e. it used to appear as text in the cell. What could cause this and what do I need to do to put it right? You wrote that the text appears as apersands (&). Did you really mean octothorpes (#)? If yes, then try formatting that cell as General--or anything but Text. Chris Mitchell wrote: > > I...

counting rows wen 2 arguments are true
is there any way i can create a formula to count the amount of rows when 2 arguments are true? i have two columns with text in them, say A1:A10 and B1:B10 id like to find a way of counting the rows when both ranges have specific text in them. eg ACCOUNT TYPE[/B] [B]FULLTIME/PARTIME ordinary Fulltime ordinary Partime ordinary Fulltime ordinary Fulltime ordinary Fulltime student Fulltime say i wanted to count the rows wen account type = ordinary and fulltime/partime = Fulltime. is this ...

Implement Help function
Hello, I have a MFC dlg-based application where I want to implement a help function. For example, in Windows XP, going to the 'Properties' of 'My Computer' will show you a window with a question button on the titlebar. Is it possible to have this in dlg-based application?? thx "Wes" <saruman@pandora.be> wrote in message news:uiWUxx5sEHA.4040@TK2MSFTNGP09.phx.gbl... > Hello, > > I have a MFC dlg-based application where I want to implement a help > function. > For example, in Windows XP, going to the 'Properties' of 'My Computer'...

HTTP and HTTPS sessions question
Some secure sites have HTTPS session stay secure from login till end of communication with site(log off). Some sites are HTTPS only when log in, after login, they become HTTP, and become HTTPS only when log off. (Yahoo mail for example, etc) What are the chances that session can be intercepted and sidejacked and traffic content recorded, especially as I know this danger really exists, and its carried purposefully and intentionally, by recording DSL traffic. http://www.google.com/search?hl=en&source=hp&q=session+hijacking+attack&aq=3&aqi=g10&aql=&oq=sess...

Exact function
I've created a simple spreadsheet. Column A is a list of 15,034 used Work Order reference numbers (many values are missing); Column C is a complete list (25,000 WO numbers). I want to get a separate list of unused numbers, but can't get the EXACT function to work. ( I believe it should place a TRUE value in column D when the numbers match.) I've used: =OR(EXACT(C1,$A$1:$A$15034)), which I replicated down column D. It gives me nothing but FALSE values, even though about half the numbers match. BTW, I do get a TRUE value if I use =EXACT(C21,A1), so it may be an array problem....

Passing Arguments
Passing Arguments Hello, I=92ve created a sub to delete and open tables. It looks like this: =91=92=92=92=92=92=92=92=92=92=92=92=92=92=92 Sub OpenCloseTable(strTableName As String) DoCmd.Close acTable, strTableName DoCmd.OpenTable (strTableName) End Sub =91=92=92=92=92=92=92=92=92=92=92=92=92=92=92=92 With the code, however, I have to close and open the same table. I=92d like to do something like this: =91=92=92=92=92=92=92=92=92=92=92=92=92=92=92=91 Sub OpenCloseTable(strOpenTable As String, strCloseTable as String) DoCmd.Close acTable, strCloseTable DoCmd.OpenTable (strO...

IF Statement Question
Hello, I have the following statement that does what I want =IF(G10=3,-3,IF(G10=4,-2,IF(G10=5,-1,IF(G10=9,1,IF(G10=10,2,IF(G10=11,3,0)))))) If the random number is between 3 and 11, a value is generated, my problem is if the number is either a 2 or a 12, I want to get another number For example Random Number List 1 List 2 2 --- 8 7 0 n/a 12 --- 4 So if a number is between 3-11,...

Offline Address book question.
Several articles about OAB referes to "siteFolderServer" attribute in AD. However I'm bit confused as to which object in AD is being referred. What I found that at least two Exchange objects has "siteFolderServer" attribute. - CN=Default Offline AddressList,CN=Offline Address Lists,CN=Address Lists Container,CN=<FIRST ORGANIZATION>,CN=MICROSOFT EXCHANGE,CN=SERVICES,CN=CONFIGURATION,DC=*,DC=local" - CN=First Administrative Group,CN=Administrative Groups,CN=<First Organization>,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=*,DC=local Can an...

Licensing question
Could a 2 lane license be used for a company that has 2 separate stores with separate databases that are on the same network? Could there be a 3rd computer on that network that could switch from database to database to run reports, order and receive for both of these inventories? Or would they need to get HQ? Pat You could look at the Multi-Location add-in from Retail Realm (http://www.retailrealm.us) . It may fit your needs, but the two locations would be considered a single store. What you are asking will not work though, two databases means 2 dongles. The third management station...

VLookup in Excel 2007
In Excel 2003, when you looked up in the Criteria table, the identifier you chose had to be on the end and in alpha numeric order. Is this still the case? If I was to vlook up in multible tables and generate my own unique information, Can I go to a table created in the vlookup and use it even if it is not in alpha numeric order? Is there a work around? I don't really understand your question, but the quick answer is that there is no change in Vlookup from 2003 to 2007. You have always been able to use a table that wasn't in order by using False as the fourth parameter....

table
with a vba macro I populated a table, the size of this table is variable in each execute this macro. and with this table I create pivot table, Database functions, but How to use all resources with all data? suggestions and examples thanks Marina Hi Marina Create a dynamic named range for your data. Insert>Name>Define>Mydata Refers to =OFFSET($A$1,0,0,COUNTA(A:A),10) The above example would create a range which contained as many rows as are present in column A, and the would be 10 columns wide. Change the formula as appropriate to your data location. Then in the Pivot Tab...

Trim Function
Hi everybody I am trying to use a macro to trim a selected range but I can't seem to get it to function - the code looks like this Range("A2:R600").Select Dim MyString, TrimString MyString = " <-Trim-> " TrimString = Trim(MyString) TrimString = "<-Trim->" End sub I would be grateful for any suggetsions on how I can get this to work Thanks in anticipation Bill Hi Bill, Try this, Dim rngTemp As Range For Each rngTemp In Range("A2:R600") rngTemp.Value = Trim(rngTemp.Value) Next Cheers A...

The hide PivotTable items functionality is gone in my excel 2000
Hi, The hide PivotTable items functionality is gone in my excel 2000, meaning I do not see any drop down box to push... Any thoughts? Brgds Endre Your file is corrupted or MS will be sending you an ad for 2007? God Bless Frank Pytel "Endre" wrote: > Hi, > > The hide PivotTable items functionality is gone in my excel 2000, meaning I > do not see any drop down box to push... Any thoughts? > > Brgds > Endre No worries... When excel had a day to cool down, it decided to show me the drop down box'es the day after. Thanks anyway! Endre "Frank ...

Charting/ Graph question
Hi all I'd like to visually display a table that contains the following table structure StaffName Software 1 Skill Level Software 2 Skill Level (10 software titles >) (around 50-60 people) What i'd like to is output a simple visual output that shows me how many people are at a particular level for the each software (but all on one chart). I imagined that it may be nice to see blobs based on the size of the number of staff at a particular skill level for the specific software. However i am not sure if this is the right way to represent this data. I've created a se...

Word Art Question
I am trying to do a circle of 8 words. I want to alternate between blue and green in each word. I have tried to do 2 semi circles of 4 words each but I don't see how to alternate colors. I see how to make all words a blend of colors or to do a pattern. Any ideas for me? I need them to be about 20pt Copy the WordArt, paste special as a Windows Metafile (.wmf), ungroup, delete the transparent background, select each letter and recolor. Regroup. A circular example here, scroll way down http://msauer.mvps.org/wordart_in_microsoft_publisher.htm -- Mary Sauer MSFT MVP http://office.m...

How to see columns from which values were not returned by HLOOKUP functions?
Hello How to see columns from which values were not returned (or were returned) by HLOOKUP functions on a worksheet? All formulas contain only HLOOKUP functions. The table_array s include all columns on the worksheet. If I'm interpreting your question correctly, one solution would be to run a Histogram of the formula results. That would show how many occurrences of each result were obtained. If you're unfamiliar with the Histogram function, you need to have installed the Analysis Toolpak Add-in (go to Tools/Add-ins and click on Analysis Toolpak), then go to Tools/Data Analysis and s...

VLOOKUP Question 03-04-10
I'm using the VLOOKUP formula and I cannot determine why there is no return on certain records, which should have a return. For instance, I execute a macro to perform the VLOOKUP on one list, which entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing has the exact same listing (ie I check =LEN and they match, I compared via an IF statement and they match), yet I get #NA. The listing are text and the range_lookup is not valued in the formula, so my thought is that the "&" character may be the issue. So, Are there any characters that...