IF,Then statement?

Hi All
I am trying to find a way to write and if/then statement. I have a
column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a
column (B) of gas MPG. I want to average each type of gas. So I was
thinking of adding a column (C) for each type of gas. If A#=C(header),
THEN place B# in C#. I can then take and average the column. I just
don't know how to write it? OR is there a simplier way to write it?
Thanks
Bill


-- 
bhowe
------------------------------------------------------------------------
bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29227
View this thread: http://www.excelforum.com/showthread.php?threadid=489502

0
11/30/2005 6:00:02 PM
excel 39879 articles. 2 followers. Follow

5 Replies
372 Views

Similar Articles

[PageSpeed] 12

Pivot table will do the whole thing for you in about 60 seconds.

Title your Stations with STAT and the mileages with GAS.

Select the entire dataset including the headers, do Data / Pivot table and 
Chart report / Next / Next / Finish

Now drag STAT into the ROW fields, and then drag GAS into the DATA area. 
Now right click on any one of the values you see and choose Field settings, 
and then on the left of the dialog box in the 'Summarise by' section choose 
'Average'.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------



"bhowe" <bhowe.1zbnim_1133374200.5748@excelforum-nospam.com> wrote in 
message news:bhowe.1zbnim_1133374200.5748@excelforum-nospam.com...
>
> Hi All
> I am trying to find a way to write and if/then statement. I have a
> column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a
> column (B) of gas MPG. I want to average each type of gas. So I was
> thinking of adding a column (C) for each type of gas. If A#=C(header),
> THEN place B# in C#. I can then take and average the column. I just
> don't know how to write it? OR is there a simplier way to write it?
> Thanks
> Bill
>
>
> -- 
> bhowe
> ------------------------------------------------------------------------
> bhowe's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=29227
> View this thread: http://www.excelforum.com/showthread.php?threadid=489502
> 


0
ken.wright (2489)
11/30/2005 6:21:20 PM
Bill,

In C2.

{=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))}

Then drag across under your headers for each type of gas (C1=Citgo
D1=Exxon etc...).  This is an array formula so to get the bracket
outside of the formula, commit with Ctrl-Shift-Enter instead of jus
Enter.



Cheers,

Stev

--
Steve
-----------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=757
View this thread: http://www.excelforum.com/showthread.php?threadid=48950

0
11/30/2005 6:21:41 PM
I haven't used this function before. How do I get the brackets
CTRL,SHFT, "Bracket", ENTER? All at once? Sorry, not very Excel savy!
Thanks
Bil

--
bhow
-----------------------------------------------------------------------
bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2922
View this thread: http://www.excelforum.com/showthread.php?threadid=48950

0
11/30/2005 7:04:17 PM
Bill,

Type in the formula  without the brackets like you would normally for
any formula(# 1) but when you would normally type enter to commit to
the formula,don't.  Hold down the Ctrl key and Shift key and hit the
Enter key simultaneously.  That will result in the curly brackets
around the formula (#2).


1.
=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))

2.

{=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))}

Cheers,

Steve


-- 
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=489502

0
11/30/2005 7:19:43 PM
Steve,
Thanks, worked like a charm.
Bill


-- 
bhowe
------------------------------------------------------------------------
bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29227
View this thread: http://www.excelforum.com/showthread.php?threadid=489502

0
11/30/2005 8:31:31 PM
Reply:

Similar Artilces:

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...

Array formula with and/or statements
Help please, this is more of a theoretical question. Can you use and + or statements within an array formula. if I have 3 columns a,b,&c and in these colums I have a list of integers < 10. If i wanted to answer the question Count the incidents where A=1, B=2 AND C=1 OR 2 OR 3 I named the columns a_ b_ and c_ for the sake of ease. I particularly need to know if I can use an array formula or sumproduct My guess of =SUMPRODUCT((a_=1)*(AND(b_=2,OR(c_=1,c_=2,c_=3)))*1) did not work with an exapmle that had 1 sollution nor did =SUM(IF(a_=1,IF(AND(b_=2,OR(c_=1,c_=2...

If statement depending on NAME of workbook
I would like certain events to occur depending on what the name of the workbook is. Is there a way to do this? What would be the code for this? Can something like if WorkbookName = "Name" Then Or am I totally out of whack here? Rob rob, You can use either ThisWorkbook.Name or ActiveWorkbook.Name e.g. If ThisWorkbook.Name = "Test.xls" Then ' do something Else ' do something else End If John "rob nobel" <robnobel@dodo.com.au> wrote in message news:eygDEsaqDHA.688@TK2MSFTNGP10.phx.gbl... > I would like certain events to occur depending on wh...

Direct statements for retirement account don't seem to update properly
Hi, When I manually update my retirement account from Fidelity by downloading a QIF statement and importing it, it works fine. However, if I do a direct update, the individual transactions do not appear. I just see lump sum adjustments in the cash account. I see a KB article here that sounds like this... http://support.microsoft.com/default.aspx?scid=kb;en-us;822766. Does anyone know if this will solve my problem and if there are any downsides to marking the account something other than 401K (which it is)? --John Hi John, First save your .mny file and then follow the procedure you fo...

combining =SEARCH & =IF statements
hello, I have a simple task i think, but i need some guidance! I have 3 Cells: A1: 12345 - 67891 A2: 12345-67891 A3: 1234567891 Here are the things I want to do: 1) Remove all spaces 2)IF the cell already has a hyphen, do nothing 3)IF the cell does not have a hyphen, insert one at approximately 5 character positions from the right. I know how to do all of these in their own cells, but I want to combine all of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks like this: A1: 12345-67891 A2: 12345-67891 A3: 12345-67891 Thank you in advance...

CRM3.0 - Workflow
Hello, I would like create a workflow process, when an opportunity is created, that send a email every week while the estimated close date is null. Is that possible ? How can we do it ? Thanks for your help. WTCSO Try something like this: Create a rule triggered from the Create event of an opportunity called "No Estimated Close Date" with the following contents: When opportunity is created Wait 1 week If opportunity.estimatedclosedate = null Send Email xyz Save the rule and test it. If it works, create a manual rule called "Rule A" for example with identical se...

Can I use a variable for a table name in a SQL statement using VBA
I'm trying to append data from one table to another using a SQL string in VBA but the table names are always changing so I want to use variables for the table names. Any ideas? Ambushsinger - Yes, you can do this. Set up variable to hold the SQL string (e.g. strSQL), and build your code into the string. For example: strSQL = "INSERT INTO [" & strIntoTableName & "] SELECT * FROM [" & strFromTableName & "];" Debug.Print strSQL The Debug.Print statement will display the evaluated code in the immediate window if you have t...

download bofa statement problems
Hi there, Recently I have been unable to use the "active statement for MS Money 2000". I get an error of "the file you attempted to import appears to be invalid or contains corrupt data. Please download the file again. If the problem continues, contact your financial institution." I am contacting b of a, but thought you may be able to help me know what to do to fix this... Thank you, Dave ...

Email Statements from Collection Module
When I attempt to email statements from the Collection Module I am getting an error message, ' Wrong number of arguments to 'RM_Statements_Update_Header' ...

IF statement / SUM IF
I have a spread sheet with the following: In Cloum A are 4-digit division numbers, In column B are 5-digit account numbers, In cloumn C are values. I am attempting to write a formula that says: If the range in column A = 1234 and Cloumn B = 12345, then Sum the value in Column C, if not zero. Any help would be greatly appreciated. Thanks. T.R. =SUMPRODUCT((A1:A15=1234)*(B1:B15=12345)*(C1:C15)) I didn't include your "if not zero" condition because if the number is zero, it won't change the sum anyways. Dan E "T.R." <anonymous@discussions.microsoft.c...

Balance Sheet and P&L Statement from AA
Dear All, I have a situation that one of my client wants to use AA to use COST CENTERS and based on that he want BS and P&L reports and he is not at all using chart of accounts segments for COST CENTERS, as far as I know the AFA and FRx do not support the AA. So kindly adviice me accordingly. Your immediate reply will be a great help. Thanks in Advance. -- Developer On Dec 28, 2:00=A0am, Dexdev <Dex...@discussions.microsoft.com> wrote: > Dear All, > > I have a situation that one of my client wants to use AA to use COST CENT= ERS > and based on that he want BS and ...

Money 2005 Statement Downloads
I have 2 accounts at the same financial institution and when I download my account information both downloads go into the same account in Money. What can I do to prevent this from happening? I use Money 2002 so I don't know the precise steps but I think this will do it. Go into Online Services Manager and click Modify Services for the account in question, then follow the onscreen steps to re-setup online services. A screen will appear where you assign each downloaded account to the correct account in Money. It sounds like you now have both online accounts assigned to the same a...

need help with an if statement
right now i am using this code on a command button; Sub Save_As_FileName() FName1 = Range("d3").Value FName2 = Range("d5").Value Fname3 = Range("d6").Value Fname4 = Range("d7").Value pth = "f:\bids\" MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname & " " & ".xls" ActiveWorkbook.SaveAs Filename:=pth & MyFileName End Sub But the pth will change, depending on what is in cell d2. so, I think i will need an If statement saying if cell d2 = ...

IF Statements #4
Hi there. I have been working on an IF statement and would like to know if you are able to add font colour as a result. For example,=IF,(G9<=4,"font.colour.Blue","font.colour.Red"). If someone knows the answer it would be greatly appriciated!! Many Thanks Shelley, You can do that with conditional formatting Format|Conditional Formatting select Formula Is And use your formula =G9<=4 and select a format with a blue font Then either use a second criteria for the alternative when G9>4 or leave that as the basic cell format. PC "Shelley" &...

How to balance?: Bank Statement is wrong
Help me, please! I've got a crazy one here. The amount my bank statement shows for my house payment transaction is wrong, but my mortgage statement is correct. The bank statement says it was $0.06 less than it really was. I have my mortgage set up as another account in Money. So how do I make a transfer transaction show that it's 6 cents less in my checking account than it is in my mortgage account? I know that it's probably not possible (nor should it be), but then what is the correct way to enter this oddity into Money to get both accounts to balance? Thanks in advance! T...

Dates in IF statements
:confused: I have a small deal I can't figure if I can do... I want to return a value for a date in a specific range of dates, is this possible? TIA, Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30622 View this thread: http://www.excelforum.com/showthread.php?threadid=502755 How is the value chosen from the range? ************ Hope it helps! Anne Troy www.OfficeArticles.com "ScottEslick" <ScottEslick.21v13m_1137637501.1941@excelforu...

Nested IF-statements
Hi group, Sinve years I use nested IF-statements in Excel. But I have always had this suspicion that I go about building them in the wrong way. Generally I use the function wizard. First of all I want to take out all zeros that would indicate an empty cell, before I get into any serious stuff. Doing that I kind of do it backwards, including a lot of copying and pasting, using notepad as a scratchbook, eventually arriving at something like this =IF(AL7=1;"";IF(AA7>0;IF(B7="L";AA7;IF(B7="S";AA7;""));"")) I wish the wizard for IF-statements...

FRx statements exported to one Excel workbook
Clients would like to export chained FRx reports to export to one Excel workbook for easier e-mailing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=5c667c48-735e-42b4-99...

Formatting Cells using an If Statement
Hi, I am wondering if anyone knows, is there a way to format a cell based on it's value using an IF statement? For example I want to enter a worksheet function that would change a cells font color to red if it exceeds a certain value. =if(A2>2, change fornt to red, do nothing) I am not looking to do this in VBA do to the various range sizes and shapes that i would have to reference (would be a nightmare), and I dont want to use conditional formatting either. It would be fine and easy to use conditional formatting, but the cells I am trying to evaluate are on different tabs (...

Self Referencing If Statements, Iterations
Previously, I created several self referencing If Statements that would return data when a toggle cell was equal to what I was looking for and would not change if the toggle was equal to something else. This had the effect of providing a data table without having to have the input functions on the same tab as the table. For example, on an excel merger model, on a cases tab I had many different "cases" (one company acquired another at a 10% premium, 15% premium, 20%, 25%, 30%, etc.). On another tab in cell A15, I had this if statement =if(CASES!$A $1=A$14,RESULT,A15) then I would have...

OR statements
What is the maximum number of arguments you can use in one (Excel 2003). Is this maximum lower in a conditional format? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) OR - maximum number of arguments: Help file says 30 Probably the same in CF. Also, you can group the arguments and effectively get more. That many arguments however, could produce a very ugly formula. <g> -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware .. .. .. "Paul Hyett" <vidcapper@invalid83261.co.uk> wrote in message ...

Coding for IIf Statement
Can anyone tell me what is wrong with this statement? =IIf(DataEntryFrm![Waive10%]=True,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved],IIf(DataEntryFrm![Waive10%]=False,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Granted])) When I run this I get #Name? in the textbox. Assuming that the form that contains the textbox with this control source expression is named DataEntryFrm: =IIf([Waive10%]=True,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved],ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Granted]) -- Ken S...

Nesting IF statements, Conditional Formatting
Hi Folks, I'm hoping someone has an easy answer. I have a dillemma I wanted to address with conditional formatting but the conditional formatting only allows for 3 circumstances. I have a column of percentages - 0 through 100%. I have a series of ranks - A through F and X. A = >0 through 20% B = 21 through 40% C = 41 through 60% D = 61 through 80% F = 81 through 100% X = 0% If the percentages are in column I, and I want column J to reflect the proper letter rank, based on I, how do I nest my formula in J to show the right information, *or* how do I use conditional formatting? I in...

Conditional statement in a Report Field ??
rptDemo is built on qryDemo Here is what I would like to know how to do in the report... If [Prime Location] = True Then Display "True" 1 - Can you put statements directly into a field in a Report? 2 - If so what is the correct syntax? 3 - If no then how? Thanks for any help. Set the text box's control source to =IIf([Prime Location] True, "True", "") (including the equal sign) Alternatively, bind the text box to the Prime Location field, and set its format to ;True; (that's a semi-colon before and after the word True) -- Doug Steele, Mi...

Options for large If/ElseIf statement
I’m trying to add value to a form control(Contract Number) using the data from a table “Contract” which has 3 fields ContractShipline, ContractCustomer, ContractNumber. I’m currently using a very long “If / ElseIf ” statement in the form to fill in the Contract Number when Shipline and ContractCustomer are equal . If [cboAsiaNoticeShipLine] = "FE Evergreen" And [cboAsiaNoticeServiceContractCustomer] = "CELSUS EXPORT LLC." Then [AsiaNoticeContractNumber] = "35378" Because the contract numbers change a lot this has become high maintenance . My thou...