I've created a Database using excel which feeds data to a pivot table I created based on it. My objective is to create a new item in this pivot table (Pivot Table > Formulas > Calculated Item) which calculates the average data for the past 6 months.
The formula I used in order to get a result is this:
and the problem is that the answer is 0 when there is no data for all 6 months (for example, there is a customer who hasn't baught anything during the past 6 months), the pivot table displays 0, instead of not showing this customer at all. My goal is for the IF function not to return any data if the result is 0, so that the pivot table doesn't show items with no data.
I also tried:
=IF(((May+June+July+August+September+October)/6)=0;"";(May+June+July+August+September+October)/6)
and also using ;" ";
but these formulas return #VALUE! in the pivot table, given that the Data is "SUM OF Sales" and "" is not a number.
How can I get the formula not to show anything at all?
In addition to this, I was wondering if it is possible to make this formula more automated, so that it calculates the average of the past 6 months, taking into account some type of reference month (rather than having to edit the formula each time a new month is analyzed).
I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.
Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.
This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.
Total AlanMon400AlanThur600TomMon200TomWed300JamesMon1000Output4002001000
What function/formula can I use if I want that function to accept 2 criterias and returns me a result. Based on the above example, if the criterias are Alan & MOn, it will return 400 and if the criterias are Tom and Mon, it will return 200 and if the criterias are James and MOn, it will return 1000. Bascially, I want a function that can accepts 2 criterias. I do not want to sum up the total of the rows that are associated with one of the criteria i.e Alan. For Alan, I want 400 instead of 1000
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
What I am trying to achieve is using the above can it check
C4:C7 Grouped and named "first" C8:C10 Grouped and named "second" C11:C13 Grouped and named "third"
If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?
I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.
Been working on this problem for a while, I need to have a vlookup (or other function) return locations based off of a concatenate (for unique identifiers). Only issue is that the concatenate formula creates doubles which returns the same location for each vlookup. I have attached an example file.
I need to figure out a formula that will look across a row and once it finds the criteria specified, it will return a value going down the corresponding column. I have attached a file showing the example.
The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.
I have tried searching for this topic and have been unsuccessful. Perhaps my search was too broad. Either way, I am trying to understand how to search/query a specific data entry among several sheets, and then return corresponding values from adjacent cells.
I created a drop down menu from a named data range on a seperate spreadsheet in the same workbook. Based on the selection from that drop down menu I want a preassigned number to pull into another cell. I'm not thinking of the correct formula or something, because it's not working. Could it be because it's text? My spreadsheet with the data is as follows:
Bridger to American Fork 051725 Hunter to American Fork 051725 Hunter to Delta 051715 Hunter to Hiawatha 051728 Navajo to Am. Fork w/ front 051725 Navajo to Am. Fork w/o front 051725
The 1st column is the origination/destination and I named that range "freight hauls". Which is what the dropdown menu on another sheet (AP Reconcilliation) consists of. From that, I want the 2nd column number to pull into another cell on the AP Recon sheet. The formula I wrote that isn't working is: =index('AP Reconciliation'!A2:B8,match(A6,'Freight Hauls List'!B2:B7,0),2)
I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:
On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.
Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data Orangegreen Blackwhite Blueyellow
Col. B - Result
Col. C - Keys Orange White Yellow
I used
Code: =INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
I have 5 coulmns of data and in col F I have the following formula in row 18 =VLOOKUP(E18,$C$18:$D$800,2,FALSE) the results come back " TAX CONSIDERATIONS" however the same results comes back in row 21 and row 24......how can I modify this lookup to change when column B changes
I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values - So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)
I have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list:
List of individuals with client numbers
123 John Doe 456 Jane Doe
List of individuals with state business
John Doe AR John Doe CO John Doe FL John Doe MS John Doe TX Jane Doe MS Jane Doe TX Jane Doe AZ
I need for those client numbers to appear in the column before the names on list with states.
Example:
123 John Doe AR 123 John Doe CO 123 John Doe FL 456 Jane Doe MS 456 Jane Doe TX
I have these numbers located in column A (rows 1:3)
formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?
I have two workbooks Raw File and Lookup_Test, I want to find the value of Column A from Raw File, in Column A of Lookup_Test, if the value if found then i want the value in Column D from Lookup_Test, to be copied to the Column B of Raw File .
Well its like using vlookup , however i am trying to avoid using Vlookup and find any other way of doing the same.
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem.
There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories.
For example: Zone 1: 0-1lb = $2.12 1.1-1.5lb = $2.12 1.6-2lb = $2.21 etc
How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction.
This is all done within a address list so each package is a row and all weights and zones will be in a separate column.
Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
I am editing macro formula... is it possible VB does calculation during code execution & return only the value to Cell. this way the formula calculation remain hidden & code will probably run faster..eg.
I seem to be having a little trouble with some of my code. It has worked previously without error. The variables CMvN and SkMvN are DIMed globally. The only thing that I recall that I have changed is that I have changed them from Double to Long. Changed it back to test and still experiencing problem.
' Filter named range using a named range on another sheet Range("FilterRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("NormalMetroAM"), Unique:=False CMvN = WorksheetFunction. Subtotal(101, Range("Cancelled")) SkMvN = WorksheetFunction.Subtotal(101, Range("Skipped"))
in the immediate window, the correct value (e.g. 8.57142857142857E-02) is returned. However, when I step through the code and go for example from CMvN line to SkMvN, the value isn't assigned to CMvN.
i have a sheet which adds up stock = SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero.
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X. If PRODUCT_A with OPTION_B, then PRICE = $Y. If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
