Conditional formula 05-12-10

trying to construct a formula for the following for the same cell:
if a2 > 6, then e2 = 0
if a2 = 6, then e2 = 1
if a2 = 5, then e2 = 2
if a2 = 4, then e2 = 3
if a2 = 3, then e2 =5

this is for a golf scoring system.  a2 values are hole scores and results 
are "handicap" point scores.  Any help greatly appreciated
0
Utf
5/12/2010 3:14:09 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1100 Views

Similar Articles

[PageSpeed] 35

Try this:

=3DIF(A2>6,0,IF(A2<=3D3,5,7-A2))

Hope this helps.

Pete

On May 12, 4:14=A0pm, desmond1412
<desmond1...@discussions.microsoft.com> wrote:
> trying to construct a formula for the following for the same cell:
> if a2 > 6, then e2 =3D 0
> if a2 =3D 6, then e2 =3D 1
> if a2 =3D 5, then e2 =3D 2
> if a2 =3D 4, then e2 =3D 3
> if a2 =3D 3, then e2 =3D5
>
> this is for a golf scoring system. =A0a2 values are hole scores and resul=
ts
> are "handicap" point scores. =A0Any help greatly appreciated

0
Pete_UK
5/12/2010 3:24:59 PM
Copy and paste the below formula in E2 cell.
=IF(A2>6,0,IF(A2=6,1,IF(A2=5,2,IF(A2=4,3,IF(A2=3,5,"")))))
-- 
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"desmond1412" wrote:

> trying to construct a formula for the following for the same cell:
> if a2 > 6, then e2 = 0
> if a2 = 6, then e2 = 1
> if a2 = 5, then e2 = 2
> if a2 = 4, then e2 = 3
> if a2 = 3, then e2 =5
> 
> this is for a golf scoring system.  a2 values are hole scores and results 
> are "handicap" point scores.  Any help greatly appreciated
0
Utf
5/12/2010 3:32:01 PM
Another approach
=Max(0,7-A2)+1*(A2=3)+5*(A2=2)+10*(A2=1)

-- 
Wag more, bark less


"Ms-Exl-Learner" wrote:

> Copy and paste the below formula in E2 cell.
> =IF(A2>6,0,IF(A2=6,1,IF(A2=5,2,IF(A2=4,3,IF(A2=3,5,"")))))
> -- 
> Remember to Click Yes, if this post helps!
> 
> --------------------
> (Ms-Exl-Learner)
> --------------------
> 
> 
> "desmond1412" wrote:
> 
> > trying to construct a formula for the following for the same cell:
> > if a2 > 6, then e2 = 0
> > if a2 = 6, then e2 = 1
> > if a2 = 5, then e2 = 2
> > if a2 = 4, then e2 = 3
> > if a2 = 3, then e2 =5
> > 
> > this is for a golf scoring system.  a2 values are hole scores and results 
> > are "handicap" point scores.  Any help greatly appreciated
0
Utf
5/12/2010 3:55:01 PM
Reply:

Similar Artilces:

restoring GP 10.0 32-bit into GP 10.0 64-bit
Hello: We are going to install GP 8.0 on a 32-bit server in-house and restore a client's 8.0 data into these SQL databases. Pretty cut and dry. Next, we will upgrade this in-house server from 8.0 to 10.0. Continuing, we will install GP 10.0 on the client's new 64-bit server. Finally, we will restore the upgraded 10.0 databases from our in-house 32-bit server to the client's 64-bit server. Here's the question. Is there anything "wrong", from a technical standpoint, in restoring SQL databases from a 32-bit environment into a 64-bit environment? Thanks! chil...

drop down menus #12
I am a new Excel user who has basically learned everyething I know by practicing and making MANY mistakes, but my question is: I use ddata validation to make drop down menus for particualar cells that I need the data in and I seem to be limited as to the amount of data I can use in the drop down menu. Is there any way I can make a drop down menu for cells that can accomodate "unlimited data"? These are basic speadsheets I am making but I need alot of selectons in a few of the drop down menus. If I gave my users a dropdown with hundreds of listed items, they'd come at me w...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

GP 10 and Terminial Server 2008
My customer is having issues with GP 10 sp4 and Terminal Server 2008. Whenever they are in one of the Navigation panes (Purchasing) and they choose an option in the navigation Pane other than the PURCHASING (ie VENDORS) they are kicked out of GP. Has anyone come across this? ...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

Test 04-05-07
Test -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ...

Sales for Outlook Installation 10-24-05
I'm having trouble installing CRM sales for outlook. The installation errors literally on the point of finishing. The message is "Setup has failed because of an error. Setup was unable to install the .msi file for microsoft crm sales for outlook". I have searched the knowledge base and newsgroups to find any info on this matter but at the moment I have struggled to find any signifcant information. The pc used is running XP pro SP2. Thanks for any assistance in advance ...

how to search in excel with condition
First I must say that your site is great and it helps me a lot. Can you explaine to me how to do search in Excel ? explenation: I have 2 sheets on the same excel file. I need to search one column in the 2nd sheet and if i found the data I look for then I need excel to put this data in the first sheet on the same raw when the data is equel to. example: 1. on sheet 1 I have names of people. 2. on sheet 2 I have a cloumn that contains some of the names from sheet 1. 3. I need excel take each raw in sheet 1 and search sheet 2 for the same name. 4. if it found the same name in sheet 2 then...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

What could keep formulas from recalculating?
I have a workbook that I received from a company with which I do business. On it are several worksheets (tabs). On one sheet, if I go to enter a formula in a cell, it displays it as text instead of calculating a value. For example, if I type =A1+A2 it will display that exact text in the cell as opposed to displaying the sum of cells A1 and A2. On other worksheets in the same workbook, entering formulas seems to work just fine. I have checked in Options, and auto-calc is checked (pressing F9 also has no effect). I can only assume that there is some setting of which I am unaware that is prev...

installing GP 10.0 on workstations only
Hello: I understand that you can install GP 10.0 and forego installation of the application on the server. If you choose to go that route, then how do you path to SQL during the first-time run of GP Utilities when you create the company databases? I mean, if you install GP on the first workstation without installing GP on the server at all, you have to tell GP the path to the database (in the Database Setup window). Can you use UNC pathing in that window or do you have to use mapped drives? Also, what sort of security do you need to create the databases from the workstation? Domai...

Macro Question #10
Hi, I am working with a text file that I imported into Excel. It is a transactional report of sales by customer. The data will have the customer number and name spread out over three cells. What I want to do is to create a macro that will contatenate all three cell and then perform a paste special and paste them into another cell in another column. Since the number of transactions by customer can vary the customer name and number can appear anywhere on the page. I created a macro to do this using the first customer which appears in cell B5 through D5. The next customer appears...

Conditional Format #14
That's what I was messing up! It was the ordering, not the formula! Thank you. Now if I have my =AND(E2="Y") for one and no formatting and my second =C2<TODAY() Red bold format, I should be able to mak rule 3 =C2<TODAY()-2 format Yellow bold and have anything 2 days clos to due yellow, YES -- Jimmytec ----------------------------------------------------------------------- Jimmytech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1447 View this thread: http://www.excelforum.com/showthread.php?threadid=26087 RE: =AND(E2="Y") You o...

Extender problem with GP 10
Hi all, I setup a Dynamics GP 10 system for a customer. The system is used from HRM staff. I used Extender to add a number of extender windows and detail windows. On the Sever all users can access the extender windows but when they login on a separate terminal server (with a client installation) the Extender windows are not accessible. Only the Extender Detail Windows can be seen. When trying to go to the windows all users including sa get the following message: "Not all required fields have been entered for the window <window name>." I went in the extender and opened ...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...

R-squared formula
I was just wondering if anybody knows the formula for the r-squared value for a power curve. Excel states that it uses a transformed r-squared value. I am trying to calculate the value by doing the mathematical calculations in the spreadsheet. I have gotten the correct equation that the chart shows, but I can't seem to get the correct r-squared value. The formulas that they give are: R^2 = 1-(SSE/SST) SSE = E(Yi-Yi^)^2 SST = (EYi^2)-(EYi)^2/n E is the best I can get to a sigma in this. Sigma is the sign to sum up all the indicated values. Yi is the original Y values. Yi^ ...

IF Multiple Conditions
A B C D E Color Total Time Total Time 2 Time Allowed Y/NO BLUE 1:22:33 2:22:33 1:00:00 BLUE 2:22:33 3:22:33 2:00:00 BLUE 3:22:33 4:22:33 3:00:00 BLUE 4:22:33 5:22:33 4:00:00 BLUE 5:22:33 6:22:33 5:00:00 RED 6:22:33 7:22:33 6:00:00 RED 7:22:33 8:22:33 7:00:00 RED 8:22:33 9:22:33 8:00:00 I need an IF formula that: - will read IF condition in the A column is Blue it will bring back...

Excel formula #9
I need some help Please. not sure how to write a formula? Description of what I want.: if cell is equal to 48 or less then I want to add 3 to the cell but if cell is greater the 49 then I want to add 6 to cell Can some one help Please. I am new with excel and am not sure how to do this.. Thanks Sun, 3 Feb 2008 19:04:06 -0800 from David franklin <DavidF@discussions.microsoft.com>: > I need some help Please. not sure how to write a formula? > > if cell is equal to 48 or less then I want to add 3 to the cell but > if cell is greater the 49 then I want to add 6 to cell ...

Increasing Month only in formula
Kindly i need help on below: i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" in Cell "A2" and "=Mar!G$2" in Cell "A3" and so on by simple dragging. is that possible? On Thu, 11 Feb 2010 12:53:01 -0800, Malla <Malla@discussions.microsoft.com> wrote: >Kindly i need help on below: > >i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" >in Cell "A2" and "=Mar!G$2" in Cell "A3" and so o...

Help: Seting the value of another cell with a formula
I know that there's a way to make a cell a particular value based on the entries of a range, or array of cells, but is it possible to do the reverse, using only one formula in a cell. Here's what I'm trying to do: What I would like to do is set one of a range of cell to have a value based on the value in A2. Example: Cell A1 has a value of 2, A2 has a value of 2007. I want A10 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2008. I want A11 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2009. I want A12 to equal...

Conditional Formatting #79
I am trying to come to grips with Conditional Formatting in Excel 2007 and would welcome suggestions for the following. If a cell contains two lines of text (normally seperated by an Alt CR) then set colour (fill) of the next cell in the row. If a cell contains only one line of text then set colour (fill) of the next cell in the row to a different colour. If a cell contains a fill of a particular coolur then remove fill from all previous cells in that row. TIA Chris -- Chris Watts chris@watts-bros.co.uk (synonymous with ctwatts1@btinternet.com ) ...

Conditional Formatting #61
I want to set up my worksheet with conditional formatting. Here is a example of my sheet (see attachment). attachment: http://www.excelforum.com/attachment.php?attachmentid=2699&stc=1 My original format for the worksheet is the text color red, I want t be able to put the date or comment in column E (Date Rcvd) and make th entire row black text. Is there a way to do this?? +------------------------------------------------------------------- |Filename: Pt Log 2.gif |Download: http://www.excelforum.com/attachment.php?postid=2699 +---...

How to convert Conditional Format into the "real" format?
Hi, does anybody know the trick to easily convert Conditional Formatting into the "real" cell format? (don't need to have conditional format anymore) Thanks Select your cells. Choose Format/Conditional Formatting... and click Delete. In article <ugg2DVbJEHA.556@TK2MSFTNGP10.phx.gbl>, "Arie Sukendro" <info@NOSPAMdrsirx.com> wrote: > Hi, > does anybody know the trick to easily convert Conditional Formatting into > the "real" cell format? (don't need to have conditional format anymore) > Thanks This will remove the conditiona...

How Many Days Since... Formula -Help
Hi, can anyone help? I have a spreadsheet tracking several different Departments in our company and how often they have an accident. I need to track how many days they go without an accident. So if Dept A had their latest accident yesterday. And if someone opens the spreadsheet in 3 days, it should say "4 days w/o an accident for Dept A. I'm sure this can be done, I just have no idea how :roll: Thanks in advance, Scotty Assuming 8/5/2003 is the start date... =TODAY()-"8/8/2003"&" Days w/o an accident for Dept A" Lance >-----Original Message----- &...