HTML Code:
=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))
I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?
In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.
I have probably over complicated?
Distance Calculations Example 1.xlsx
I need quickly identifying multiple locations based on their proximity to an initial location and each subsequent location selected. I have attached an example spreadsheet that is structured in the following manner:
Cell H2 is the 'initial' location that will be used to identify all subsequent locations
Colums A through C are location specific details including latitude and longitude of all 450 possible locations
Column D is a distance calculation based on the difference betwen the location in column A to the 'initial' location in cell H2
Cell F2 is the necessary minimum distance between locations(in miles)
Cell G2 is the number of locations needed(this will vary)
What I am trying to accomplish is to fill column H with the number of locations specified in cell G2 that are atleast the minimum distance in F2 apart from each other, like drawing virtual circles around the locations on a map.
I am pretty sure this can be done with a Macro, but am not sure if that is the best solution or not. I am a novice Excel user, so even though I don't know the solution, I can already see two issues:
1. Whatever the solution is, the reference in column D will need to change to calculate the distance for each subsequent location instead of staying absolute with cell H2(H3 for second location, H4 for 3rd, etc...)
2. With each selection that is made, any location less than the specified distance in cell G2 should be removed from any future choices for subsequent locations.
Do we get the option in excel to copy from different locations (just copy a part from say Sheet A, Sheet B and some other excel file) and then pasting all at once in some other file?
View 2 Replies View RelatedI am currently trying to automate some excells workbooks that my company uses. The one i'm working on is a listing of all the change orders we have. Unfortunatly there are roughly a 1000 rows, each containing the information for the change- change #, Date opened, part numbers, change description, status, date closed.
Order 0001 - 10/22/08 - 0156, 7251, 9901 - delete bag - closed - [blank] -
Order 0002 - 10/22/08 - 0018, 0612, 0875 - add notes - open - [blank] -
What I want to do is type a part number in, it checks to see if there are any rows that already have that number and have an open status (as apposed to closed) and then tells me where the conflict is. so in the above example if I typed in 0612 it would tell me that number is already in use. I am using excel 2003 if that makes any difference.
I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))
and
=IF(WEEKDAY(F2)=1,E10,IF(WEEKDAY(F2)=7,E10,'J:DailySeptember 2006[Cash Flows -Sep06.xls]1st'!$I$40*-1))
As you can see the month is shown in 3 ways...
September 2006
01Sep2006.xls and
Sep06.xls
I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............
I've been using the following macro ....
View 8 Replies View RelatedIm having a problem finding a formula that will count and take an average of multipul ifs.
On sheet 1 of the attached file is multipul queries raised for us to answer.
On sheet 2 is an empty table which i would like help formulating.
In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D).
I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.
I have several Workbooks ( named 110807, 180807 upto 100508) which include many worksheets(Lets say named Mon, Tue, Wed etc). The format of these workbooks is identical but the numbers within are different. I also have a separate " Totals" workbook in the same folder within which I need to calculate the average of all H15 cells across the whole range of Tue worksheets but only if Cell H3 in the same Tue worksheets is >0. The problem seems to be asking for a =AVERAGE(IF formula but no joy.
View 2 Replies View RelatedI need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
I have to manually cut/paste each excel doc which belongs to a specific Store folder, and copy then into their own shared directory, so for example:
Lets say my company has 10 Stores, and I know their online folder locations, the first step I guess would be for me to map the locations of each store, I am not sure what the best way to do this is, would I use the Case select ? to make the set = ?
So for example, Stores 1 - 10.
Set 1 = Dir("Z:Northwest1")
Set 2 = Dir("Z:Northwest2")
Set 3 = Dir("Z:Northwest3")
Set 4 = Dir("Z:Northwest4")
Set 5 = Dir("Z:Northwest5")
Set 6 = Dir("Z:Northwest6")
Set 7 = Dir("Z:Northwest7")
Set 8 = Dir("Z:Northwest8")
Set 9 = Dir("Z:Northwest9")
Set 10 = Dir("Z:Northwest10")
I am not an expert on Case Select, but was thinking:
Select Case myStores
Case Is = 1.......................
The code below works. Now I need that while it does the backup file in the same location, we also took into C: Backup_Contabilidade. Finally also put the date and time.
VB:
Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application
.CommandBars("Cell").Reset
End With
Application.DisplayAlerts = False
[Code] .....
i am counting hotels in different area of 1 city
city area hotel_name hotel_name hotel_name
NY st-1 A001 B001 C001
NY st-2 B001 C001 D001
NY st-3 D001 E001 A001
count by checking NY and see how many branches A001 is having in all these areas. (i should get answer 2)
I have a spreadsheet on a server, password protected, and certain columns, rows locked...
This is shared with 6 others and can only be accessed one at a time.
If It so that when the user saves the spreadsheet it saves over itself on the server it's located AND on the individual users computer as backup; how would I go about doing this!?
the attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
View 10 Replies View RelatedI have many spreadsheets that use shapes such as arrows and other symbols. The process for these spreadsheets is to move the shape (arrow for Ex) to indicate a specific location of pain or something like that.
Once the arrow is in position, the spreadsheet is printed to PDF and and a snapshot (in adobe) is "taken" and the picture (with the arrow) is pasted into our application.
My questions is how can I move the shapes back into position when you want to do it all over again.
When I have a few shapes I use
PHP Code:
Set sShape = ActiveSheet.Shapes("Right Arrow 6") 2
With sShape
.Top = 220
.Left = 91
.Rotation = 20
End With
For each shape
But if I have lots of shapes, 50+, it's a bit more cumbersome.
Can I loop through the shapes when I enable the macros to pick up the current positions and use that information when I do a reset (loop back again to move any shape that were touched back to where it was when the spreadsheet was opened?
Currently I have them close the spreadsheet without saving and just reopen.
I made an analysis table that shows a list of prices on a certain company. I have a table on one worksheet and I made a chart (from that table) on another worksheet. I'm using an in-cell drop down list on the table worksheet to change to different companies. Is there a way that I can put the same drop down list on the chart worksheet? Essentially, I want to be able to switch the company on either worksheet and have it change on both worksheets. I'd like to keep the chart and table uniform and running together.
View 6 Replies View RelatedI'm trying to determine the net displacement between GPS locations and am wondering how I can easily code this in Excel. As far as I know, you need to calculate the distance between Point B to Point A, Point C to Point A, Point D to Point A, and so on. The following is the formula that I would use:
A
B
C
X_Coordinate
Y_Coordinate
123456
3700000
Point A
123460
3700010
Point B
Using the following formula (or setup), this is how I would calculate net displacement:
(123460-123456)^2+(3700010-3700000)^2 = (16)^2 + (10)^2 = 256 + 100 = 356
I assume if I want net squared displacement, I would square root this value SQRT(356) = 18.867
If my formula is correct above, I wonder why Excel gives me an answer for the following net displacement formula (same as above just using Excel formula): (A3-A2)^2 + (B3-B2)^2 = 116
Using this same formula, I could incorporate SQRT in as follows: =sqrt(((A3-A2)^2) + ((B3-B2)^2)), which results in 10.77. This value is clearly different than the value above (18.867) thus I wonder if I'm just mis-calculating something in Excel.
i have a User form that i'd like to add preset tabs command locations to.
For example,
on open i'd like the active cell to be on cell M3. and as the user presses the tab button jump to the following cells locations:
P3
Q5
P7
Q7
P9
I have to select about 200 odd files from a folder, and move them to a different folder. The problem is, I have to manually select the 200 files from a list of approx 10,000 (not in order).
I have a list of the filenames (with extension) in an Excel Spreadsheet, and I'm wondering if there's a way to automatically move the files using an Excel macro or something?
example:
File location = C:EBDumped
and I need to move the 200 files to C:EBSent
So I would need to move ONLY the files where the file name is in the Excel list.
The file type (extension) is always the same, but the name (and length of the name) is different.
Is this possible using Windows XP with Excel XP?
I have a list of grade from 2~4 (i have long list but for sample i made it short). I want to get average of all grades one by one as well as by making some groups. Like 2,3, "2~4" (all groups) then "3~4". All the things were going nicely with a drop down menu. But when i put average on grde "3~4" only then the problem occurred. Although I've done my work but still I think there must be some improvements.....
View 6 Replies View RelatedI have a table that has the data below
Month Jan Feb Mar Apr
Brand 0 1 2 3
I want to do a vlookup with match to find the month and then return the value of a rolling 3 months.. So if i look up Apr i want to look at the rolling 3 prior months average ie, average of (Jan,feb and March). i have the vlookup formulas working, its when i include the multiple vlookups (Match -1, -2 etc, the average is calculating the blank cells for some reason..
I have a large set of data. I need to be able to search this data routinely using an origin and destination zip code and sum the search results. Is this possible in Excel?
View 5 Replies View RelatedI have 1 list with two columns, where column A is the location (a 3 digit code), and column B is the name (a 4 digit code).
I have a second list with two columns, again column A is the location, and column B is the name.
I want to make sure the locations and names in the second list match what I have in the first list. If they don't match, I somehow want to flag this in a third column.
I am looking to do find the latest date in a list of dates but have several problems:
1) The dates are all in one column on worksheet 1, but are sourced from other worksheets. So even though it looks like '8/7/09', the value of the cell is 'Worksheet2!B4'. This is making the MAX function not work
2) How to deal with the blank cells in the MAX function?
I use the below code to specify where a database is:
View 2 Replies View RelatedAre there any alternative places to download MOREFUNC.XLL? The link at the Morefunc for Excel site is broken, and the forums seem currently busted as well.
View 2 Replies View RelatedI have a spreadsheet where there are two drop down menus with various options. Based on those two inputs, my spreadsheet calculates a "savings" for each row. Now, I want to have cells that will keep a running total of each combination possible. I tried:
=IF(AND(A2:A37="OKC",C2:C37="AF33"),SUM(G2:G37)) but it didn't work...
A column is where you can enter 1st option
C column is where you can enter 2nd option
G column is where the savings results for those combination of inputs is populated.
I need a cell in say, J column, to sum all numbers from G column, where the combo of "OKC" and "AF33" exist.
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
Any way that I can do this in Excel?