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
346 Views

Similar Articles

[PageSpeed] 16

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:

If...then statement
Windows98 Excel 2000 Is it possible to write an if...then statement instead of a formula? In the cell C43 on the Budget 2003 worksheet I would like to put in the if...then statement: =if ('2003'!C44) is not null then ('2003'!Q44) Or is there a better way of doing this: If there is a value in cell ('2003'!C44) then I need the value that is in cell ('2003'!Q44) put into cell ('Budget 2003'!C44). Thanks for your help, Julie In the budget sheet cell C43 put =IF('2003'!C44,'2003'!C44,"") in C44 budget sheet =IF('2003...

Testing an if statement and placing result in different cell
Does anyone know if there is a way of checking if something is true and if so placing a number in another cell - Something like: =if(x>=y,c3=a1+a2,0) ??? Don't know if I would use the if statement here or not - any help much appreciated...:D No - worksheet functions can't change values in other cells. However, in C3, you could put: =IF(x>=y, A1+A2, 0) In article <RuudsRightFoot.swxxz@excelforum.com>, RuudsRightFoot <RuudsRightFoot.swxxz@excelforum.com> wrote: > Does anyone know if there is a way of checking if something is true and > if so placing a nu...

FRx and Full Financial Statements
Does anyone have any experience producing full financial statements, including footnotes (with tables) in FRx? How do you do it? We don't need to Edgarize it, just want to make the process more efficient without losing any formatting funtionality that we get in Word. Any comments appreicated! It is my understanding that FRX will only pull data from your General Ledger database. This limits some of the types of reports that you can make with FRx. Thanks - I found out today that Report Manager may do what I need. It requires GP/FRx Professional, we are on GP Standard so I can'...

a long thread of if-then statements
Hi. I'm using MS Excel Xp. Here's my problem: :confused: I have a large flatfile of our sales people and I need to do a pivot table on them. but before that, I need to create a new column which contains certain tiering of their earnings. For example: Name: Earnings Tier Person A $5000 $0 - $10,000 Person B $34,500 $30,001 - $40,000 Person C $1M $990,000 - $1,000,000 Person D $2.5M $2,490,001 - $2,500,000 The column "Tier" is the one I need to create. How do I create a long IF-TH...

Printing Statements?
I'm a new user. Printing my first statements. How Do I get rid of the Minimum Payment field or change the amount to show the current balance? -- JD Hi JD - Thanks for your note. In order to modify the account statement, you will need to modify the XML file. The file name and path can be found in Store Operations Administrator, in the Configuration dialog, Templates tab. Typically, the file is Statement.XML and is located in c:\program files\microsoft retail management system\store operations\receipt templates folder. Once you find the file used for your account statements, locate...

If Statements #3
Hi all I am new to this, can someone, please help me with this. I wont to now how to evaluate two or more logical conditions if they are true or false using if functions for example. If total purchases in A2 are greater than $7000.00 deduct 15% from those purchases and if purchases are greater than $5000.00 deduct 10% of purchases otherwise purchases stay the same. How would I do this if funtion, Please, maybe use a or somehow? Cheers Mark -- burkey ------------------------------------------------------------------------ burkey's Profile: http://www.excelforum.com/member.php?actio...

Download Statements
I just opened an account at Scottrade and have two holdings in the account. Whenever I download statements, I have two items for the account at Scottrade even though the heading at the top of the page says "you have no unread statements." I click on that row and am taken to the "Review investment holdings" screen. I see my two stocks and the only thing I can do is click "finish" which I do, and am returned to the screen showing all accounts and any unread items/statements. I could do this process 1000 times in a row with the same results. What is this ...

If Statements in Access
Hello I am currently using the following IF statement in Excel however i will need to convert it so that it will work for me in Access =IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working ",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"") Can someone please help Thanks Longbeach LongBeach, I take it you're updating a text control with text according to the value of [Expiry Date] vs. the current system Date... And, you don't need the If [Expiry Date] <> "". If it's...

Iff statement to show pictures
Is it possible to use Iff statement to show pictures Actually I am trying to use tow different pictures on the form if the value is less than 0 or higher than 0 example =IIf([Text135]<0,"","Pictue B") =IIf([Text135]>0,"Pictue A","") How can I Do This so far i am unable to fix this issue Regards Aamer You can nest IIf statements. It's not really clear what you're trying to do, since your sample code doesn't actually assign a value if the value is less than 0. Assuming you want Picture B if it's...

help with syntax for if and statement
hello all, i am having trouble figuring out the syntax for an if / and statement. this works =(IF(A1=0,"Input Required",B1)) here's the logic i want: IF cell A1=0 AND cell A2<=350000 THEN return cell value of "Input Required" ELSE A3. i know this is a basic if and statement, but i just can't get th syntax figured out. any ideas? thanks -- snsy ----------------------------------------------------------------------- snsyg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=523 View this thread: http://www.excelforum.com/showthread...

Advanced month deposit should be shown in the statement after PTR
Posting for Kenny Wu Of Professional Advantage PTY LTD. Details: After running PTR on a given cut-off date (e.g. 8/31/2007). Transaction's Apply date field like 9/31/2007 should still be shown in the RM statement eventhough you have posted them on or before the PTR's cut-off date. ---------------- 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-...

How to add DESCRIPTION statement in DEF in vs2005?
In vs2005 when I add a DESCRIPTION in .DEF of a win32 program, the compiler says warning LNK4017: DESCRIPTION statement not supported for the target platform; ignored while EXPORTS in .DEF is still accepted. Is there any other way to add DESCRIPTION? In msdn it says that DESCRIPTION is compiled as a string in .rdata, so I tried adding this at the beginning of the main source file, but it seems of no effect: #pragma const_seg() const WCHAR* pName = L"my description"; #pragma data_seg() ...

How to include check no. in Monthly Statement
Hi there, How do i include the check no. of the payment made by customer in the monthly statement? thank you. Regards lim ...

Global statements on footer of all emails
Does anyone know how to place a message on the footer of emails globally? I am looking for a way to place the following message: "This electronic message is privileged and confidential material. This message is intended solely for the individual or entity named above. If the receiver of this message is not the intended recipient, please be advised that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately and delete it. Thank you." On everyones mai...

IF Statement....If False how ignore referenced cell
Hi, Is there a way using a "IF" statement that when the statement is false it ignores the cell it is writing to. For example: If(A1=1,B16,ignore) When it looks at cell A1 it will write what is in cell B16 (let's say 7). Next I want to change cell A1 to a 2 (because my value in B16 has changed), which makes the original IF statement False and typically overwrites the value in the cell with False, " " or "0". I would like it when FALSE, to leave the value 7 and not overwrite it.. Hope this makes sense. Any help would be greatly appreciated. Hi th...

Error on CurrentDb statement
I have a db that errors everytime it hits the CurrentDb statement. the error is: Description: Automation error The specified module could not be found. Error #: -2147024770 or -2147024770 Method 'CurrentDb' of object '_Application' failed This is the first line of code that it happens on: Set rst = CurrentDb.OpenRecordset(sql) If I jump past that section of code, it errors on the next line with CurrentDb in it. Any help will be GREATLY appreciated. Specific Version Information and project type will help (is this an MDB or ADP project) "sk" <...

select statement ,sum with calculation
Hi I have a Invoice table Record 1 Fred 10.00 Jack 25.50 Jill 67.00 Discount 10.00 Record 2 Jill 15.75 Fred 12.74 discount 12.99 etc Is it possible to apply the discount asscoiated with record and then sum all records? I hope I have expllained it well. Appreciated any help Thank You. Based on your description this looks like a denormalized table, like this: Table1 --------- Name1 Amount Name2 Amount Name3 Amount Discount ---------------------------------------------------------------------- Fred ...

Restart Money 2004 when downloading statements
I seem to be having a problem when downloading any bank statement... Everytime the download is complete and I click "ok", Money tells me it has encountered a problem and needs to close. The Exception Information is as follows: Code: 0xc0000005 Flags: 0x0000000 Record: 0x0000000000000000 Address: 0x00000000105b7885 I've tried repairing the file (through standard repair) and also tried msmoney.exe -s. It fixed a couple of other errors (i.e. outstanding transactions that did not exist... although this is still an issue with one account). I don't know wha...

Collections Management Default Statement ID
In GP 10, we've assigned a Default Statement ID in the Collections Management Local Setup window. However, when we go to the Collections Management Print Selection window and put a checkmark beside Print Statement, the Statement ID is not defaulting in. This worked in GP 9. Did this break in GP 10? Or, are we missing something? ...

Help!! How do I fix mistake in statement download set up
I have Money 2004 Premium. I have 2 accounts with the same account number. One for 2004 and the other fro previous years. The first time I downloaded a statement from my bank Money asked me if I wanted to change the account to download the data to. I mistakenly didn't specify the correct account. How do I change things to specify the correct account? Thanks much, Joe See http://umpmfaq.info/faqdb.php?q=150. "Joe Shannon" <anonymous@discussions.microsoft.com> wrote in message news:227c301c45d82$491d11b0$a301280a@phx.gbl... > I have Money 2004 Premium. I have 2 ac...

Easy income statement question
I have a quick question regarding the income statement. Under "Revenue", are those values based on the time of sale or when we actually get paid for those services/product? And, under "Cost of Revenue", are those values based on the time of sale or when we actually get paid for those services/product? Thanks for your time. Good questions and the answer to both depends upon if you are using accrual-based accounting or cash-based accounting. In an accrual world, you generally don't worry about what's been paid by a customer. If you post a customer invoice, ...

2 WITH statements
Doing this in SQL 2005. How do you connect 2 WITH statements. I'm trying to use (;) or (,) and neither is working. What am I doig wrong. I'm just tyring to get the second WITH table to show up. Thanks from a newbie ******************************************************************* WITH MtdCount(TraderID, MTD) AS (SELECT Commission.TraderID, COUNT(Trades.Broker) AS MTD FROM Trades RIGHT OUTER JOIN Commission ON Trades.Broker = Commission.TraderID WHERE (Trades.TradeDate >= DATEADD(mm, DATEDIFF(mm, 0, @TradeDate), 0)) AND (Trades.TradeDate <= @TradeDa...

how do I make multiple "if" statements
I want to do something like this: if cell D4 is "23" then "45" or if cell D4 is "24" then "48" or if cell D4 is "26" then "97" etc. So the formula in one cell is looking for the value in another cell and returning the appropriate response. Thanks! Hi =IF(D4=23,45,IF(D4=24,48,IF(D4=26,97,""))) The formula returns Null "" if the value in D4 is not equal to any of the values posted. Change the "" to whatever you would like in this case. Regards Roger Govier AccessNewbie wrote: >I want to ...

Setting A blank as a result of an IF statement
I want to create a chart with a trendline which takes the results of a If statment as data input. Column F is date (used as the X AXIS) an column G then contains values on certain days but not all days. Like this =IF(ISBLANK(G4),G4,SUBTOTAL(9,G$2:G4)) SO I get a running total. Where the cell in column G is blank the result is 0 I also tried using a zero-length string ie =IF(ISBLANK(G4),"",SUBTOTAL(9,G$2:G4)) - This shows a blank in the result cell but the chart still plots it a a '0' I tried changing the plot options for chart to skip blanks but i doesn't work. Is this a...

If statement not displaying text if conditions not met
I want to do a calculation which calculates selling price from either Margin % or Margin £ and if there is nothing in either field then I want to display the text "no margin". I have tried the following formula:- Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin"))) However, if there is nothing in either field, then it returns #Error. Anyone got any ideas? What happens if you change "no margin" to 0 (zero). Does it then work? -Dorian "Ruth&quo...