Formula Account For All Possible + Or - Figures
Nov 23, 2007
I 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?
View 9 Replies
ADVERTISEMENT
Sep 7, 2012
Creating 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] .........
View 1 Replies
View Related
Aug 21, 2009
I 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 Related
Mar 15, 2009
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in
D4. So,formula needs to be in (D4, E4, F4) there are other datas in the worksheet
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...]
it repeates the same fomula as D4 and does not adjust for the increase in column number.
View 3 Replies
View Related
Mar 23, 2009
Please see attached two books,
The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)
I have to manually manipulate this report everyday and extract some information from it to
a custom report(macro report) that we use everyday.
I have created a macro for this - please see my code below.
I need some help with some small issues I am having,(Highlighted in yellow)
1) I need to include a Total line, but the size of the report varies everyday from say
around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below
the report, please see attached for an example.
2) I need to hardcord a formula in on Column F and G. But again the report varies in size,
the columns do not change but there could be lots more rows. how do i take this into
account
The formula i need to hard code into column F is
=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007
)+IF($D$60>10000000,0.0005)
D60 in the above formula is just the total of all the other months summed together.
For Column E the formula is just =F*E But again the size in the report varies.
View 13 Replies
View Related
Apr 2, 2013
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
View 9 Replies
View Related
Jul 28, 2014
I want to calculate the sum of a column but I have 4 lots of figures going down the column and I only want it to calculate the some of 2 of those 4.
The 4 figures: £4.80, £3.80, 31 & 25.
These 4 figures are within the column multiple times and I just want to have one cell that sums up the £3.80's and £4.80's within the column and then another cell to do a separate sum of the 25's and 31's.
Is this possible, if so how? Does it require an IF SUM formula of some sort?
View 5 Replies
View Related
Oct 10, 2008
I have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....
Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.
View 13 Replies
View Related
Dec 9, 2013
I am using Excel 2007, I have a customer sheet and a sales sheet in the same work book. My customer sheet lists all customers with a single entry only each with a unique customer code. My Sales sheet lists at an item level but does include the customer code so if my customer has purchased more than one item the customer code can be repeated on the Sales sheet on multiple rows.
When I do a vlookup to display the amount each customer has spent on the customer sheet, only the very first sales figure is displayed, if further down the sales sheet the same customer has made another purchase I want this figure to be added to the first figure and all I want to see on my customer sheet is the final total figure.
The vlookup formula I am currently using is:
=VLOOKUP(G2,Sales!$A$2:$B$1785,2,FALSE),
I'm thinking maybe a Vlookup will not do what I require, maybe I need a different formula/function?
View 8 Replies
View Related
Jun 17, 2014
In the attached workbook, see the "P & L" tab, cells I30 to I48. The idea of the formula I have placed into these cells is to drag out the figures from column I in the "Trial Balance" tab of the workbook. A similar formula is used succesfully in the "balance sheet" tab in Column B. I don't understand why the formula doesn't work in the cells i've specified in in the " P & L" tab it seems to work for I8 and the rows immediately below it "revenue".
UK Trial Balance May 2014 V2.xlsx‎
View 5 Replies
View Related
Jan 7, 2014
[URL]
attached is a returns sheets that a qty is logged against part number every week. Some of the data I can use pivots on ( no problem ), but I have an issue on the returns tab where I think index/match / sum or something else will be better. At the moment because of the layout of data I have to drag each week number into the pivot value, do a greater than zero filter then summarize as a sum (yuk) I just cant get my head around index/match /sum. It would be great just to be able to enter a week number, and all the warehouse, part numbers and qty came through. There is a short explanation on the returns tab of what im looking for.
so in essence I want week numbers across the top, but be able to enter a year on the returns tab that looks at the week number. I don't know enough about the formula to change.
View 14 Replies
View Related
Nov 28, 2006
I need a formula to check two times and count the colum figures.
example only:
countif(=>4:30:00 PM <=5:30:00 PM)*(11/12/06)
So, I need to count all cells with time between 4:30 and 5:30 with the date of 11/12/06.
View 14 Replies
View Related
Aug 5, 2008
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?
View 9 Replies
View Related
Jun 7, 2014
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
View 2 Replies
View Related
Feb 27, 2009
I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula:
=AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
View 4 Replies
View Related
May 21, 2006
to 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)
View 9 Replies
View Related
Aug 15, 2013
Attached 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.
View 9 Replies
View Related
May 11, 2009
I 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.
View 6 Replies
View Related
Dec 9, 2009
I’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 Related
Feb 26, 2009
I 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?
View 10 Replies
View Related
May 31, 2007
If 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
View Related
Aug 19, 2007
The 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.
View 9 Replies
View Related
May 16, 2006
I 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.
View 3 Replies
View Related
Jan 4, 2007
I'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
View 4 Replies
View Related
Jan 3, 2014
I 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 Related
Apr 20, 2009
I 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 Related
Feb 15, 2012
ABaccountamount60000-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.
View 2 Replies
View Related
Apr 6, 2013
I have account numbers in Col F and values H on sheet1
I want to set up a formula on sheet account classification to add the values in Col H based on a range of account numbers from 3000 to 3017.
View 6 Replies
View Related
Sep 23, 2007
I receive a large text file each month from a vendor, This file contains account numbers, account descriptions and costs associated with the previous months period. There are 5 separate business units included in this report. I am looking for a way to translate the account numbers pprovided by the vendor to fit with in the account numbers we use. A Vlookup will work by creating a Map that translates their numbers to match ours, but this would need to be copied in monthly and as there are 15k rows or more it will likely take a while to process. I am wondering if others have attempted this and have advise as to what works best.
My plan was to create an Excel file that reflects the account numbers provided with our account numbers and then use a VLookup to match the data received with this master file as it were. ( So I would have a file called AccountMap which contains 4 columns, Cols A & B is the info provided by the vendor and Cols C & D are our codes and descriptions ). Then when the data arrives I would enter my vLookup and go from there.
Presently the steps are:
Open Vendor Text File in Excel
Copy a Business Unit and related Data to new WorkSheet
Insert Vlookup to map the vendor accounts to our accounting system format
Rearrange the data to meet our import requirements
Export the modified range as a Text file
Import into Accounting system
View 3 Replies
View Related
Mar 16, 2014
I want to create leger accounts In cashbook there is account head (ex. Hand Loan A/c, Salary A/c, Exam A/c and so on)
View 2 Replies
View Related
