change the formula by changing contents of cell

I have a table that ranks a list of players by their statistics, for example, 
when I change cell B2 to "walks", my formula (large) lists the player with 
the most walks in cell B4, 2nd most in B5, 3rd in B6...  that works well 
because more walks are good.  But, when I change cell B2 to "strikeouts" I 
want it to list the player with the fewest number of strikeouts in B4, next 
fewest in B5 and so on.

Is there a way to change the "large" function to the "small" function within 
the formula?  

I have set up a helper cell (C2) that changes from "large" when I have 
"walks" in B2 to "small" when I have "strikeouts" in B2.
0
Utf
2/19/2010 4:31:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
796 Views

Similar Articles

[PageSpeed] 35

Maybe you can incorporate a multiply in your formula:

 * 1 for when you select walks, and

 * -1 for when you select strikeouts.

If you supply your formula then we can see how this could be included.

Hope this helps.

Pete

On Feb 19, 4:31=A0pm, AJSloss <AJSl...@discussions.microsoft.com> wrote:
> I have a table that ranks a list of players by their statistics, for exam=
ple,
> when I change cell B2 to "walks", my formula (large) lists the player wit=
h
> the most walks in cell B4, 2nd most in B5, 3rd in B6... =A0that works wel=
l
> because more walks are good. =A0But, when I change cell B2 to "strikeouts=
" I
> want it to list the player with the fewest number of strikeouts in B4, ne=
xt
> fewest in B5 and so on.
>
> Is there a way to change the "large" function to the "small" function wit=
hin
> the formula? =A0
>
> I have set up a helper cell (C2) that changes from "large" when I have
> "walks" in B2 to "small" when I have "strikeouts" in B2.

0
Pete_UK
2/19/2010 4:44:40 PM
Hi,
You can use an if statement

=if(B2="Walks",your large formula,if(B2="strikeouts",your small formula))

"AJSloss" wrote:

> I have a table that ranks a list of players by their statistics, for example, 
> when I change cell B2 to "walks", my formula (large) lists the player with 
> the most walks in cell B4, 2nd most in B5, 3rd in B6...  that works well 
> because more walks are good.  But, when I change cell B2 to "strikeouts" I 
> want it to list the player with the fewest number of strikeouts in B4, next 
> fewest in B5 and so on.
> 
> Is there a way to change the "large" function to the "small" function within 
> the formula?  
> 
> I have set up a helper cell (C2) that changes from "large" when I have 
> "walks" in B2 to "small" when I have "strikeouts" in B2.
0
Utf
2/19/2010 4:55:01 PM
This does work, but my formula is rather lengthy as it is.  I was hoping to 
find a shorter alternative so that I can use it (changing the function used 
based on what I type in a cell) for a number of other situations.

"Eduardo" wrote:

> Hi,
> You can use an if statement
> 
> =if(B2="Walks",your large formula,if(B2="strikeouts",your small formula))
> 
> "AJSloss" wrote:
> 
> > I have a table that ranks a list of players by their statistics, for example, 
> > when I change cell B2 to "walks", my formula (large) lists the player with 
> > the most walks in cell B4, 2nd most in B5, 3rd in B6...  that works well 
> > because more walks are good.  But, when I change cell B2 to "strikeouts" I 
> > want it to list the player with the fewest number of strikeouts in B4, next 
> > fewest in B5 and so on.
> > 
> > Is there a way to change the "large" function to the "small" function within 
> > the formula?  
> > 
> > I have set up a helper cell (C2) that changes from "large" when I have 
> > "walks" in B2 to "small" when I have "strikeouts" in B2.
0
Utf
2/19/2010 5:09:01 PM
{=IFERROR(INDEX(Batters, MATCH(LARGE(IF(INDIRECT(B$1)="x", INDEX(Batters, , 
MATCH(B$2, INDEX(Batters, 1, ), 0)), 0), $A4), INDEX(Batters, , MATCH(B$2, 
INDEX(Batters, 1, ), 0)), 0), 1), "")}

-Batters is the large table from where I get the data.

-Cell B$1 is a position indicator that I've set up so that I can sort 
through only first basemen, second basemen, etc.

-Cell B$2 is the stat indicator (walks, strikeouts, hr...)

-Cell $a4 is the rank (so that when I copy down, $a4 is 1, $a5 is 2, $a6 is 
3...)

So when reading this way, in cell B4 the guy with the most of a stat is 
listed, in B5 the second most and so on.  If a guy does not fit the position 
parameter, then his stat is counted as a zero and he won't show up at the top 
of the leaderboard.

What I would like, is when I change the stat indicator (B$2) to one in which 
in which I want a small number (strikeouts), the formula changes from "large" 
to "small" and in the if portion, the "0" for a false statement changes to a 
"1000" (so that if a player doesn't meet the position parameter, his stat is 
counted as 1000 and he won't show up at the top of the leaderboard).

As I said before, I've created helper cells so that when I change the stat 
in B$2, a "large" or "small" will come up in one cell, along with a "0" or 
"1000" in another cell.  I was hoping to incorporate these into the original 
formula (tried using an indirect-type function), but was unsuccessful.

Thanks for the help so far (and in the future).


"Pete_UK" wrote:

> Maybe you can incorporate a multiply in your formula:
> 
>  * 1 for when you select walks, and
> 
>  * -1 for when you select strikeouts.
> 
> If you supply your formula then we can see how this could be included.
> 
> Hope this helps.
> 
> Pete
> 
> On Feb 19, 4:31 pm, AJSloss <AJSl...@discussions.microsoft.com> wrote:
> > I have a table that ranks a list of players by their statistics, for example,
> > when I change cell B2 to "walks", my formula (large) lists the player with
> > the most walks in cell B4, 2nd most in B5, 3rd in B6...  that works well
> > because more walks are good.  But, when I change cell B2 to "strikeouts" I
> > want it to list the player with the fewest number of strikeouts in B4, next
> > fewest in B5 and so on.
> >
> > Is there a way to change the "large" function to the "small" function within
> > the formula?  
> >
> > I have set up a helper cell (C2) that changes from "large" when I have
> > "walks" in B2 to "small" when I have "strikeouts" in B2.
> 
> .
> 
0
Utf
2/19/2010 6:24:07 PM
Reply:

Similar Artilces:

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Email with no address, subject or content
Please bear with me if this subject has been covered ad nauseum, but I frequently receive email with no "From:" address, no subject and no apparent content. I'm running Outlook 2002. Can anyone tell me what's going on here? Is it an attempt to plant a Web bug? Regardless, I would like to create a rule that automatically dumps such mail in my spam folder. Using the rules wizard I see how to redirect email from a specific address, but leaving the address field blank doesn't work. Thanks in advance for any help. mb ...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...