I found a reference on Ozgrid.com that allows me to sum values located every 2 columns. My need now is to confirm how to do that for even and odd columns:
{=SUM(--(MOD(COLUMN($H16:$EU16),2)=MOD(COLUMN($H16),2))*($H16:$EU16))}
I think this is the correct formula to sum even columns.
I am compiling odds for football matches using the last 16 match outcome data for each team. Basically I need a solution or formula that counts the number of home wins, away wins and draws for each team in the last 16. This would basically mean allowing for the latest match outcome added into and then eliminating the 17th match back so that formulas from the last 16 matches only are always calculated. I envisage having a the data in a row of of 16 columns for each eam.
View 9 Replies View RelatedCreate a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.
View 9 Replies View RelatedWhen exporting a set of historical sports results to Excel 2003 the numeric odds are converted to a date eg 7/2 changes to 07-Feb. Can this be prevented or adjusted to retain the original data.
Also if I may ask can the UK style odds be converted to a decimal style. Eg. 7/2 (UK) needs to be converted to 4.5 ( Decimal equivalent )
Basically all I want to do is paste some odds data in to excel and then get it to just calculate the odds for me example below
9/4 Exotic Isle, 5/2 Greenery, 5/2 Port Alfred, 6/1 Reqaaba, 12/1 Somoud
I know I can use text to columns using the deliminator to split them in cells however I just want to take the odds
So Exotic Isle would be 2.25 (9/4) Greenery would be 2.5 (5/2) and so on
I've tried using the left function basically all sorts.
I would like to randomly pick a name based on percentage. In the example below Deb takes up 50% of the wheel, if you were to spin the wheel she would have the highest chance of landing on the dial. How you could make excel randomly pick a name based these odds. The list will have over 50 names and each name will be earning points throughout the year. The more points you have the higher percentage of the pie you get and the higher your odds off getting picked in the lottery.
View 9 Replies View RelatedI have a line graph which I use in conjunction with live horseracing odds. On the vertical axis are the odds which are sourced from a worksheet. Each horse is represented by a data series that moves along the horizontal axis. The refresh rate is determined by the user embedded in the code.
I would like to expand the length of each data change along the horizontal axis as the data can be volatile. Sometimes there are rapid changes on the vertical axis which makes it difficult to interpret. With each refresh the line data moves about 3mm. It would be much better if it were about 5mm, especially as I now use a widescreen display.
There are no values on the horizontal axis as such, only time is suppose set by Excel?
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.
View 2 Replies View RelatedI have four columns of data, as follows:
label 1, value 1, label 2, value 2
I need to create a formula in the fith column that for each line will tell excel to:
look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.
I have tried doing this with SUMIF but am getting nowhere fast....
I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).
View 4 Replies View RelatedIt's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
I have file with so many columns and i want to keep only columns i want.
Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb
expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM
Example:................
The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.
View 3 Replies View RelatedI found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Sub CopyToA()
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
ActiveCell.Offset(0, 1).Select
Loop
End Sub
However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View 2 Replies View RelatedI have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.
I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.
i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .
View 6 Replies View RelatedThis is a piece of a spreadsheet meant to track patient Range of Motion performance over a max of 4 weeks. How do I calculate % Improvement [write the formula for column J] to account for the fact that sometimes a patient is discharged after being seen 2 weeks OR 3 weeks or 4 weeks. I don't want to change the formula every time.
The SUMMARY % Improvement in J7 - will be changed to the average of each individual patient % Improvement from column J.
Looks like I need a dynamic range from what I can see from Googling. Do I need to use Offset ?
Have a spreadsheet with 15 columns. In one of the columns is the name of the company and that column is not in alphabetical order. The city, state, zip code, business type and all the other pertinent data about that company is in the same row as the name of the company. My intent would be to put the company names in alphabetical order and keep all the company information in the same row as the company name.
View 1 Replies View RelatedI have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies View Related