Forumla To Get The Average Per Account?
May 31, 2007If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?
View 9 Replies
ADVERTISEMENT
Complex If Statement To Calculate Date Range From Create Account To Stop Account
Sep 7, 2012Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
Mapping Account . How To Add Additional Data In To Account#
Aug 21, 2009I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies View RelatedForumla For Rounding Up And Down
Apr 25, 2007I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies View RelatedForumla Result When Displayed
Jun 18, 2009I have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?
View 2 Replies View RelatedMultiple IF And SEARCH Forumla
Aug 19, 2009I am trying to do is use a combination of IF and SEARCH forumlas to return a result based on a choice of constants available to the user. Issue 1: IF Function to determine which worksheet to SEARCH What I would like to add to this is if one of the 3rd criteria (Amount) is under a certain number then the results are derived from the 'Staff' worksheet'. (This is stage I have gotten to so far). However, if the criteria is above a certain number then the results are derived from the 'Executive' worksheet. This is the part I'm so far unable to do.
Issue 2: SEARCH function returning the column per the user's selection on 'Matrix' page In addition, criteria 2 (Type) can be selected which corresponds to a column in the Staff and Executive worksheets. So far I have been unable to get the correct search results to post in the Matrix page. What I mean by this is that currently it is set to Type1 only. I have tried using an IF function in cell G8 and include this in the formula, however, the forumla returns the contents of G8 in the cell instead.
Issue 3: SEARCH results are post in an accending order. At this stage I have only been able to work how how to do this by using an AUto Filter on the Staff and Executive pages and sorting accending that way. Going forward as updates are made to the staff and executive pages I would prefer that this manual task was not required. Instead, the formula used returns the search in accending order.
Sum Forumla - Not Working When Fill Down
Jul 20, 2009Basically I have cells like this.
.....
I want to fill this formula down so that the summing occurs all the way down the collumn. However when I fill it down, it just copys the first two forumlas, rather than make the formula correspond to the row.
Exchange A Number For A Letter Forumla
Aug 8, 2009I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
=If (forumla) = Colour Fill Cell
Oct 5, 2009I have two dates lets call them cells A1 and A2. I then have another cell that caluclates the difference in these dates into a simple number (ie not another date) - A3. I need one of two things.
Ideally I want the whole cell (A3) to appear as a Green (fill) cell if the value is below 5, appear Orange if beween 6 and 9 and appear as red if its 10 or over.
I do not know if you can colour a cell that has a formula in already. So what follows is my current 'work around', which, if you can not colour the A3 cell, then I would need this cell coloured (A4). =IF(D12>7,"Urgent Attention",IF(D12<5,"New",""))
Margin Forumla :: How Do I Figure Out The Cost?
May 27, 2008I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies View RelatedUse A Forumla To Find A Cell One Back
Feb 19, 2008I have a worksheet with pricing & part numbers on sheet1. I put a formula on sheet2 that puts the lowest price in colum C of sheet2. Now I need to also get the part number from the cell before. So if the lowest price is in cell E3 I need to also get the part number in D3, and put it in B3.
Countif Forumla That Counts If One Cell Is Greater Than Another
May 20, 2009I basically want for it to be able to calculate all the games a each team has won in the example below.
View 13 Replies View RelatedUsing A Forumla To Return The Position Of A Value Like Index Match
Jun 16, 2009I have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies View RelatedForumla Highlight A Entry Based On Count If
Jan 21, 2010If I minus H is less that 90 count as 1. But if I minus H is greater than 90 count as one but highlight the row on the speadsheet in yellow.
What I am doing is I have a list of about 3000 entrys and I need to determine if (I minus H is greater or less than 90). I then need to count how many times out of the 3000 entrys each entry is greater or less than 90 and highlight the entrys in yellow that are greater than 90.
It would be ideal to determine the difference between I minus H and then categorize the findings of the 3000 entrys to see how many were say 59 and how many were 47 and how many were 92 etc.
I have never seen a forumla highlight a entry based on count if. I am currently rapidly searching the forums here but to no avail.
A Forumla To Look At A Year And Month And Return A 1 If Match Found?
Apr 3, 2014In cell b2 I have a formula =text(today(),"yyyy") which daisplays todays date as a year, in cell b3 I have =text(today(),"mmmm") which displays todays date current month.
I have a column of Months and Years
January 2014
February 2014 etc
I need todays (current) month and year to indicate in a helper column next to the relevent month and year as a number 1 to use as a reference to return date from the row where the 1 is displayed, I have tried =if(and(b2=a10,b3=b10),1,"")) and it dosent like the fact that the year in the colmuns is entered as standard text, is there any whay to do this?
Forumla For The Calculating Total TIME But To Give A Value As 0 When Text Is Typed
Apr 14, 2009I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
4 Areas North East South And West- Forumla To Enter Into A Cell
Jan 19, 2009i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
Forumla To Give A Starting Number And Take One Away Each Time A Code Or Codes Are Dislayed In A Cell Range
Mar 22, 2009In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
Account Reconcilition
May 21, 2006to write a macro to put the background color where a certain figure sum is zero. Eg. Column A contain as
5
-5.25
12
-5
-12
If one select A1 to A5 and run the macro the color for all cell should be green except for A2. Infact the macro will point out which amount is not deposited into the bank. (All +tives are for Deposits and all –tives are for sales)
Revenue Summation By Account Name
Aug 15, 2013Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
Counter For Transactions Within Account
May 11, 2009I need to have a running counter of transactions within an account.
Solved with: C2=COUNTIF($B$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3
I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.
Account For “negative” Time
Dec 9, 2009I’m trying to get the difference between times, BUT I want to account for “negative” time. So if I project that an employee will finish a task at 2 am, and they finished at 2:30 am, I want the result to be -:30, not just :30. If they finished at 1:30 then it would just be :30. Hence, my dilemma. I can get the difference, but didn't think Excel could recognize negative times. Right now I am using =MOD((A1-A2),1) or =(A1-A2+(A1<A2))*24/24 and in cells A1+A2 I am using military time.
View 2 Replies View RelatedVBA To Fill References ='Account (1)'!$C$5
Feb 26, 2009I need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.
i need
='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5
down to
='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5
is there a way to imput these references using VBA script?
Expense Account Lookup
Aug 19, 2007The expense account we work with involves several currencies due to the international nature of the business. With that, each row must show the currency involved and the formula used from one line to the next makes it repeat the currency until changed i.e. if cell B22 has a date entered then Cell H22 will reflect the currency from Cell H21 =IF(B22="","",H21). If Cell H21 showed CDA for Canadian currency then H22 would also become CDA. Once changed manually then all cells below will now reflect the new currency until changed again.
Using the lookup function we collect the individual amounts of each currency and run totals at the bottom. Therefore, if there were three entries in SGD (Singapore Dollars) and two entries in EUR (European Euros) etc. that each row will do a lookup by the three letter currency code and collect the sum of each currency. SGD could repeat later again and when changed manually will be included in the lookup.
At the present we are entering the three digit codes manually i.e. SGD. What we would like to do is automatically have the bottom be able to pickup the currency change when a new currency is entered on the individual rows on the top part of the expense sheet. So if Cells H21:H23 were SGD and Cells H24:H25 were ERU that Cell A58 would show SGD and cell A59 would somehow be able to grab the ERU etc. One of the same currencies may repeat itself and the lookup will capture the additional totals but SGD would only show once.
Formula Account For All Possible + Or - Figures
Nov 23, 2007I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.
I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)
Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?
Creating Account Spreadsheets
May 16, 2006I have agreed to create a simple accounts system for a friend of my mothers. I have no problem when it comes to manipulating cells to interact ( sums) and then creating an aesthetically pleasing spreadsheet. However, as with all things excel, there is an easier way to doing something and a person usually doesn't realise that till the end.
I was hoping that somebody might be able to direct me to a resource that would be ideal for constructing a 12 month revenue and expenses account. I just want it to be as simple as possible for the lady that I am doing this for (all I want her to do is enter how much she made for one day and how much she spent), and everything around this I can make interact.
SUM Account For Added Rows
Jan 4, 2007I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.
How can I add all totals while still allowing for a new row to be inserted in any group?
eg:..............Row E
1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00
Sum Of A Column Based On Different Account Codes
Jan 3, 2014I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies View RelatedNumber Formatting (in Account Numbers)
Apr 20, 2009I currently have account numbers formatted 1.234.5678 and need to convert them all to 1234.5678. how this can be done without manually keying 22,000 account numbers?
View 3 Replies View RelatedSumif Last Two Digits Of Account Equals To 60
Feb 15, 2012ABaccountamount60000-502,503.4060000-601,143.4860000-70847.7260000-80282.1860000-85238
.1870000-50156.8560000-89200.0080000-80300.0090000-70400.0070000-6020.00
i need formula that will sum column "B" if the last two digits of column "A" = 50,60 or 70.