Separation Of Results Depending On + / - Total
Jun 20, 2014
I have a grand net income and net loss rows and underneath those are costs which are like the breakouts of the income and loss. I need a function that spits out a separate total income (meaning sum of all the positive numbers of those breakouts) and separate total loss( meaning the sum of all the negative numbers within those breakouts).
In addition, I have a separate table (on my spreadsheet it starts on cell B13 and that table has the total for each of the breakouts. I want to have a function on that table too that when the number is negative, the number will appear on the Net Loss column. If it's positive, then it appears on the grand income column.
View 1 Replies
ADVERTISEMENT
Apr 22, 2009
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
View 9 Replies
View Related
Jun 18, 2014
I'm trying to figure a function wherein if I change the category, the results will automatically changes. see attached sheet.
View 6 Replies
View Related
Aug 25, 2008
Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.
View 3 Replies
View Related
Feb 27, 2008
I am trying to have a cell populate verbiage depending on the total % of the cell to it's left. I have created this formula but it's not working.
=IF(F25=100,"Meets Expectations")+IF(F25>100,"Exceeds Expectations")+IF(F25
View 9 Replies
View Related
Jan 27, 2013
I use Windows Vista and Excel 2007.
So to cut to the chase :
On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.
Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5
Then I have two columns following those: Col AG, Col AI
In each one of those two columns, I am trying to sum up a total value using The IF Function.
In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :
=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.
But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.
This is the way it looks in the spreadsheet:
Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value
And so on 4 more times across.
I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.
View 9 Replies
View Related
Dec 30, 2006
I have a worksheet of data that I have created by combining several other workbooks into one using a macro. On this worksheet I run AutoFilter to select certain rows and then copy these rows to another worksheet, again using a macro. How can I get the macro to generate a total for one of the columns? I would like the Sum to be two rows after the end of the column with some kind of descriptor preceeding it.
View 6 Replies
View Related
Jun 5, 2007
I am trying to pull information from one spreadsheet into another. The unique identifier is used several times and I need to pull ALL of that data over. I using a vlookup currently but it is taking only the first line of the unique identifier and not the other lines that are on the original spreadsheet. Again, I need to pull all of the data from the original spreadsheet to the new spreadsheet. What would the formula for that be?
View 2 Replies
View Related
Oct 16, 2008
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View 3 Replies
View Related
Apr 24, 2008
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
View 9 Replies
View Related
Jun 10, 2008
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
View 9 Replies
View Related
Mar 26, 2009
Id like to have a running total of each sailors inspection results. So if he's had 5 outstandings, 10 sats, and 3 fails during his 18 week course, it will display this at the end of his row. Mind you, its a class of 30 for 18 weeks so there are a ton of cells. I tried multiple check box macros which assign them great but cant total the selections. Same with selection buttons.
View 9 Replies
View Related
Feb 14, 2009
I don't know if the title explains it, but I was given an Excel Directory type file (name, address, telephone etc.) which has been giving me issues with various exports because of how it's formatted.
Specifically there is one names column with "Last Name, First Name" and then a "City, State Zip" column. I'm hoping that there is a way I can separate the "Name" column by extracting just the last names and copying them to a new column. Likewise I would like to be able to extract City, State and Zip and put them each in their own columns. Is this even possible to do?
View 4 Replies
View Related
Aug 31, 2010
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. how I can prevent Excel from separating the text into rows?
View 3 Replies
View Related
Mar 19, 2009
I am using Excel to calculate distances from latitude and longitude GPS positions. These positions have a format as follows:
S36 39.113 E174 25.945
These have been imported into a column, and using VBA I wish to separate latitude and longitude into two other columns. In both cases the minutes will eventually have to be express as decimals of the degrees. I don't mind if numerous columns are used as stages to get the final answer which is 36.234678 in one column and 174.15567 in the other.
View 14 Replies
View Related
Aug 19, 2013
I receive a monthly spreadsheet from our trucking company which tracks the package and weight of that package. However I need to pull the weight out and run a report on total weight. However it is a mix of pounds and kilograms. Please see my example. What I want is a final column of all weight converted to kilograms. Note as well the spacing in the data as sometimes the "lbs" is right next to the number and other times there is a space between them.
Raw Data
package 1
10.2kgs
package 2
5 kgs
[Code] ........
View 3 Replies
View Related
Aug 21, 2013
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
View 3 Replies
View Related
May 14, 2014
I want to write a formula/script that searches file names in Column "A" for an item number located in Column "B", then combines all file names containing that item number into Column "C" with comma separations. Each item number will have a different number of file names associated with it (between 1 and 10 files), and there are about 2000 different item numbers being crossed with about 7000 file names.
Here is an example completed with a CONCATENATE formula:
File Name
59481A_1.jpg
59481A_2.jpg
59481A_3.jpg
59481A_4.jpg
59481A_5.jpg
59481A_6.jpg
Q110XL_1.jpg
Q110XL_2.jpg
C5710_1.jpg
C5710_2.jpg
C5710_3.jpg
C5710_4.jpg
Item Number
59481A
Q110XL
C5710
Final Image List
59481A_1.jpg,59481A_2.jpg,59481A_3.jpg,59481A_4.jpg
Q110XL_1.jpg,Q110XL_2.jpg
C5710_1.jpg,C5710_2.jpg,C5710_3.jpg,C5710_4.jpg
View 4 Replies
View Related
Apr 21, 2014
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
View 1 Replies
View Related
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
View Related
Jan 7, 2010
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
View 2 Replies
View Related
Mar 5, 2014
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies
View Related
Dec 21, 2008
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies
View Related
Dec 13, 2013
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies
View Related
Jun 13, 2014
I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls
View 7 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
Oct 10, 2009
In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.
I don't know if it is possible or not and am working on a pre-existing worksheet.
View 9 Replies
View Related
Sep 13, 2009
Sub sumbotton()
Dim ar As Range
Dim rng As Range
Set rng = Selection.CurrentRegion
Set rng = rng.Resize(rng.Rows.Count + 1)
rng.Rows(rng.Rows.Count).Select
For Each ar In rng.Areas
ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"
Next ar
End Sub
View 9 Replies
View Related
Oct 15, 2007
I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
View 9 Replies
View Related
Jun 9, 2014
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies
View Related