#### need formula help

```I'm trying to have a formula do 3 scenerios in one cell and have a partial
formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
greater than 0 and less than or equal to 7 use 3000.  I would like to
continue the formula that if G39 is greater than 7 and less than 14 use 5000
and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 7:48:02 PM
excel.misc 78881 articles. 5 followers.

8 Replies
600 Views

Similar Articles

[PageSpeed] 30

```Hi,

=IF(AND(G39>0, G39<=7),3000,IF(AND(G39>7, G39<=14),5000,if(G39>14,7500)))

"tom" wrote:

> I'm trying to have a formula do 3 scenerios in one cell and have a partial
> formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> greater than 0 and less than or equal to 7 use 3000.  I would like to
> continue the formula that if G39 is greater than 7 and less than 14 use 5000
> and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 7:54:01 PM
```=IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)

care to what is more important to you..  1st items will cancel later items,
even if they are also true.  you can change the order in which they occur.

"tom" wrote:

> I'm trying to have a formula do 3 scenerios in one cell and have a partial
> formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> greater than 0 and less than or equal to 7 use 3000.  I would like to
> continue the formula that if G39 is greater than 7 and less than 14 use 5000
> and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 8:00:08 PM
```IF(G39<=0, "", IF(G39<=7,3000, IF(G39<14,5000, IF(G39>14,7500,"G39 is equal
to 14"))))
--
David Biddulph

tom wrote:
> I'm trying to have a formula do 3 scenerios in one cell and have a
> partial formula working: IF(AND(G39>30, G39<=7),3000) which equates
> to if cell G39 is greater than 0 and less than or equal to 7 use
> 3000.  I would like to continue the formula that if G39 is greater
> than 7 and less than 14 use 5000 and if G39 is greater than 14 use
> 7500.  How can I do this?

```
 0
David
2/5/2010 8:02:54 PM
```thank you!

"Eduardo" wrote:

> Hi,
>
> =IF(AND(G39>0, G39<=7),3000,IF(AND(G39>7, G39<=14),5000,if(G39>14,7500)))
>
> "tom" wrote:
>
> > I'm trying to have a formula do 3 scenerios in one cell and have a partial
> > formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> > greater than 0 and less than or equal to 7 use 3000.  I would like to
> > continue the formula that if G39 is greater than 7 and less than 14 use 5000
> > and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 8:04:01 PM
```correction on formula, needs 2 more parenthesis
=IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)))

"Dan" wrote:

> =IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)
>
> care to what is more important to you..  1st items will cancel later items,
> even if they are also true.  you can change the order in which they occur.
>
> "tom" wrote:
>
> > I'm trying to have a formula do 3 scenerios in one cell and have a partial
> > formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> > greater than 0 and less than or equal to 7 use 3000.  I would like to
> > continue the formula that if G39 is greater than 7 and less than 14 use 5000
> > and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 8:06:01 PM
```Maybe a bit shorter

=LOOKUP(G39,{0,8,15},{3000,5000,7500})
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"tom" wrote:

> I'm trying to have a formula do 3 scenerios in one cell and have a partial
> formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> greater than 0 and less than or equal to 7 use 3000.  I would like to
> continue the formula that if G39 is greater than 7 and less than 14 use 5000
> and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/5/2010 8:07:02 PM
```Does this segment make sense: AND(G39>30,G39<=7),... ?
Micky

"Dan" wrote:

> correction on formula, needs 2 more parenthesis
> =IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)))
>
> "Dan" wrote:
>
> > =IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)
> >
> > care to what is more important to you..  1st items will cancel later items,
> > even if they are also true.  you can change the order in which they occur.
> >
> > "tom" wrote:
> >
> > > I'm trying to have a formula do 3 scenerios in one cell and have a partial
> > > formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is
> > > greater than 0 and less than or equal to 7 use 3000.  I would like to
> > > continue the formula that if G39 is greater than 7 and less than 14 use 5000
> > > and if G39 is greater than 14 use 7500.  How can I do this?
```
 0
Utf
2/6/2010 8:05:01 AM
```The 30 is a typo.  If you look at the OP you'll see that it refers to zero.
--
David Biddulph

????? (????) ?????  wrote:
> Does this segment make sense: AND(G39>30,G39<=7),... ?
> Micky
>
>
> "Dan" wrote:
>
>> correction on formula, needs 2 more parenthesis
>> =IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)))
>>
>> "Dan" wrote:
>>
>>> =IF(AND(G39>30,G39<=7),3000,IF(AND(G39>7,G39<14),5000,IF(G39>14,7500,0)
>>>
>>> care to what is more important to you..  1st items will cancel
>>> later items, even if they are also true.  you can change the order
>>> in which they occur.
>>>
>>> "tom" wrote:
>>>
>>>> I'm trying to have a formula do 3 scenerios in one cell and have a
>>>> partial formula working: IF(AND(G39>30, G39<=7),3000) which
>>>> equates to if cell G39 is greater than 0 and less than or equal to
>>>> 7 use 3000.  I would like to continue the formula that if G39 is
>>>> greater than 7 and less than 14 use 5000 and if G39 is greater
>>>> than 14 use 7500.  How can I do this?

```
 0
David
2/6/2010 11:03:41 AM

Similar Artilces:

MultiLine Code Builder, Need Help to Clean Code:
My end goal is to create multi-line code that I can paste into VBE. Currently I'm pasting code like this... If Worksheets("vessel").Cells(CurVesl,8).Value Then Else frmVesselLog.DTPICKER1.value = Worksheets("vessel").Cells(CurVesl,11).Value End IF For example Column D is:If Worksheets("vessel").Cells(CurVesl,8).Value Then Column E is : Else Column F is: frmVesselLog.DTPICKER1.value = Worksheets("vessel").Cells(CurVesl,11).Value Column G: End IF Column H is row1 If Worksheets("vessel").Cells(CurVesl,8).Value Then row2 E...

using a UDF in place of a cond. format, b/c I need 4, not three
I know this is an age old topic here, and I'm really hoping that I don't just get referred to another web page [that type of help of course has its place and merits]. And if I had the time, I would have more fun going through docs and notes to find out how to do it, but time is not something I can barter. What I'm looking at is: I'm thinking along the lines of an example where I normally set the borders of my columns [except if it's the first row] in a cond. format via something like this: =AND((NOT(ISBLANK(\$A1))),(ROW()>1)) But, if I need that space for a Con...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Need help converting date time to date only
I have a column in my spreadsheet with date and time, you can see in m attachment. I want to convert this column in to date only. Pleas help. Thanks Geng Attachment filename: sales - open orders test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=57210 -- Message posted from http://www.ExcelForum.com Hi without looking at your file you could use the following formula in a helper column =INT(A1) and format this cell as 'date' -- Regards Frank Kabel Frankfurt, Germany > I have a column in my spreadsheet with date and time, you can see in ...

need to find the file that Outlook emails are stored in
there is one file that contains all the outlook email and information. i need help to find it. also, how can i view Hidden Files? thanks arthur Open Windows Explorer and select Tools | Folder Options. Select Show Hidden Files and Folders and uncheck the option to hide protected operating system files. OK out and open the search panel (f3). If you are using Windows XP, you need to configure the search to look for hidden items. If all goes right, you should find your *.pst file(s) under \documents and settings\<userid>\local settings\application data\microsoft\outlook. -- Neo [...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

PocketPC Outlook help
I am a new user to PocketPCs and I am trying to sync Inbox information from one computer to another. Is there a way to sync inbox information to another computer without having to delete partnerships or not having the inbox sync at all with another computer? Hi - Not sure what you mean by "from one computer to another" - do you mean you're trying to sync your PDA to more than one computer? Note that this can often cause problems and lost data. Probably best to post in m.p.pocketpc for more help.... Frank wrote: > I am a new user to PocketPCs and I am trying to sync &...

Office 2007 Professional: Help Files Issues
Gents, I cannot find a solution to this odd issue and before I submit a support ticket I thought I would ask the community. We have office 2007 professional (volume licensing) on a Window 2003 terminal server. We have a few users out of a bunch that get the "This page is unavailable" I have tried the Refresh and back. I've tried online and offline modes. In offline I get the table of contents to the left but same unavailable message when I click the links. I noticed on testing some other users, that apparently never used the help. That there are some prompts...

Formula Problem
Hi I'm struggling with a formula in an excel sheet I'm working on. I recently created a time sheet for the people in my work. One person however get public holidays a different method from everyone else - she only gets half a working day. My initial formula that works fine is =IF(OR(B12="A/L",B12="S/L",B12="P/H"),7/24,IF(B12="FLEXI",0,C12-B12- D12)) My ammended ones is =IF(OR(B12="A/L",B12="S/L"),7/24,IF(B12="P/H",3.5/24),IF(B12="FLEXI", 0,C12-B12-D12)) However it doesn't want to w...

Help with macro #5
The following macro puts the file names on the worksheet that has the command button. How can I put the file names on a different worksheet? Private Sub CommandButton1_Click() Dim FN As String ' For File Name Dim ThisRow As Long Dim FileLocation As String Application.ScreenUpdating = False FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls" FN = Dir(FileLocation) Do Until FN = "" ThisRow = ThisRow + 1 Cells(ThisRow, 1) = FN FN = Dir Loop End Sub ...

HELP!! Publisher won't open
I have Publisher 2000. When I click on the icon on the desktop, the program opens to the catalog page. When I click on 'existing files'. the program closes down. I can open the existing file in Windows Explorer, but as soon as I click on something, it closes down. This is a relatively new computer running Windows XP Media Center. I have been able to access my existing file before on this system. Can someone please help? Can you open Publisher in Safe Mode? Do you have a default printer enabled? Insert your 2000 disk, select repair. -- Mary Sauer MSFT MVP http://office.micros...

IF formula problem
I need to add categories to a downloaded bank statement. I would use something like Pascal's CASE formula: CASE(lookup value)= a: do x b: do y c: do z etc... Excel doesn't have such formula. Therefore, I use the following nested IF formula: =IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$2,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$3,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$4,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$5,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$6,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!\$A\$7,B73)),"&...

Excel Formulas
I have a spreadsheet showing whether customers have paid their debts or not. Each row represents a customer and details of the debt and the last cell in the row states whether the debt is "PAID" OR "UNPAID". Where the word "PAID" appears in a cell, I want to write a formula that will move that whole row to a different location on my sheet. I eventually want to record this action on a MACRO, so that every time I run a MACRO on my accounts spreadsheet it will take the updated information of customers marked "paid" and put in a seperate spreadsheet. Ple...

Vendor Changed Names and Need to reassociate items with new Vendor
We have a situation were a couple of Vendors have changed names through acquisitions and we need to create a new Vendor ID for these vendors. What we would like to do is copy the items we have associated with the old vendor to the new vendor so we do not have to recreate all the items again. Is there a way to create a new vendor ID and copy the items linked with an existing vendor ID to the new vendor? Thanks JTT Great Plains has a tool called Professional Services Tool, contact them. The tool is not free. It will do exactly what you are looking for. "JTT" wrote: > We ...

computing formula according to criteria #3
Thanks, That's a good idea, the problem is I have several participants, and need a template sheet where I can just paste each participant's data and the means and SD's will be computed automatically. At the momen going over each and every participant is taking me hours. I really need a formula... -- lior ----------------------------------------------------------------------- liory's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1049 View this thread: http://www.excelforum.com/showthread.php?threadid=26138 another way to do it would be to write a ...

I dont seem to find the way how i can do this report which i really need . I need to have a report that can print only 3 records , this i was able to achieve but the issue is that i need the report to be able to print either 3 consecutive records where i specify the first record and the others be consecutive or else ( this i also need) be able to chose the 3 different records . Guys i hope i gave a good explanation Thank you -- Access For Fun See if this example helps: http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1028860&Zf=f48&Zw=batch&...

dates in formulas
I would like my spreadsheet to add a month to a cell based upon the value of the date in another cell. In other words, I would enter the date in A1 and be formatted as Oct-2009, and I would like to put a formula in A2 that would result in the display of Nov-2009. I've tried =A1+1 but that just won't get it as it still returns Oct -09. "Russ" <Rusty@alwaysathome.net> wrote: > I've tried =A1+1 but that just won't get it as it > still returns Oct -09. Because you added one __day__, not one month. Try one of the following: =date(year(A1),1+month(A1),da...

How do I protect only formulas in Excel?
Hi, I am looking for a solution to be able to allow some cells that are unlocked to have other users enter data into them, however I don't wan't other users to be able to enter formulas without a password. I have heard of this being done before but I didn't know if it was an option I've overlooked or some kind of advanced VB code. Any help is greatly appreciated, RH Hi, Just an idea: try catching OnChange event and check if = is entered and then popup a password dialog Hope this helps. Peter -- http://blog.jausovec.net "RHmcse2003" je napisal: >...

help...on changing the system administrator role.
The system throw me an error for system admin role when i click save. the rest of the roles, i can save. why ?? the trace stack is as below : [COMException (0x8004140b): Exception from HRESULT: 0x8004140B.] Microsoft.Crm.Platform.ComProxy.CBizSecRoleClass.ReplacePrivileges(CUserAuth& Caller, String RoleId, UInt32 Count, CRolePrivilege[] Privileges) +0 Microsoft.Crm.Application.Platform.Role.UpdatePrivileges(String roleId, String privilegeXml, Int32 mode) +387 Microsoft.Crm.Application.Platform.Role.InternalUpdateAndRetrieve(String id, String xml, String columnSet) +170 Mi...

formula to caculate an age
I am needing a formula that would figure out the age in years and months of a specfic person. For example Date of Birth is 7/27/1999 and the date is 10/23/2004 I would like the the cell to respond as 4-2 4 being the year and 2 being the month. Have any suggestions? Gayle You need the DATEDIF Function. See Chip Pearson's site for info and usage. http://www.cpearson.com/excel/datedif.htm Gord Dibben Excel MVP On Thu, 29 Apr 2004 15:35:15 -0700, "Gayle" <anonymous@discussions.microsoft.com> wrote: >I am needing a formula that would figure out the age in >yea...

Auto-Reply and various other server side solution needs
Hello, I have been asked to provide a solution by which e-mails sent, either from within the organization or without, to a single smtp address, such as support@someplace.com, may: 1) Be distributed to a list of e-mail addresses 2) Be archived for retrieval if and when necessary -public folder?...mailbox? 3) Have an auto-response message sent to the sender 4) Be responded too by one of the distribution list members on the behalf of the smtp address the message was sent too In addition to this my customers would like to be able to author e-mails and send them as being from the support@someplac...

IF OR AND in same conditional formula
I'm setting up a conditional formula to use in a conditional format, where depending on a couple of different conditions, the cell with the date entered will be color-coded either red, yellow or green. In this particular formula, I'm using the following variables: - cell C7 = a percentage (70%) - cell CI7 = the number of days calculated from today (19) I've constructed the formula as such (makes sense to me, but Excel doesn't like it) =IF(AND(CI7<=30,D7>=70%),(OR(AND(CI7>=90,D7<=50%)))). Essentially, I'm asking Excel to evaluate two conditi...

help of setting the distance between 2 markers
Hi, In the chart of excel, there is one type called line with markers displayed at each data value. Because my data values are lots, in my chart, markers are close to each other even overlapped. I don't want this. So I want to know if Excel has the function that can set the position of markers. For example, I want to put markers at 100th data value, 200th data value, 300th data value and so on. Ye This code sets a marker at every 100th point of your first series: Sub SetMarkerDistance() Dim mychart As Chart Set mychart = Application.Worksheets("Sheet1").ChartObjec...

Changing cell location with a formula
How can change the cell locations within a formula. Here is my formula =SQRT((B8-B7)^2+(C8-C7)^2) I want to be able to substitute user inputs for the B7,B8,C7, and C8. Please let me know how to change those locations to variables. thanks in advanc -- Message posted from http://www.ExcelForum.com If you mean the users would type in a the cell addresses in other cells you should look at INDIRECT or OFFSET -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "woknick >" <<woknick.1a3tb5@excelforu...