Sum And Divide Three Different Sets Of Numbers To Get One Answer
Dec 16, 2013
I have three different sets of numbers on a r12m that I have to add and divide by different numbers and not quite sure I have the formula right as when I do it manually on a calculator it comes out different.
The formula I have put together is
=sum(O4:z4)/10)+(sum(o5:z5)/200)+(sum(06:z6)/1000)
This is the three rows of data
221121
2333443201142
402448314034292331351663
Using the formula above the result is 0.81 but if I do it manually on a calculator it is 1.6 (by doing each line individually and adding results)
View 6 Replies
ADVERTISEMENT
Jun 22, 2008
I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
View 9 Replies
View Related
Oct 5, 2007
I have a simple formula that adds two numbers and divide the result by another number
K1: SUM(E1,F1) / H1
K2: SUM(E2,F2) / H2
K3: SUM(E3,F3) / H3
K4: SUM(E4,F4) / H4
K5: SUM(E5,F5) / H5
etc, etc
and then I average the results
AVERAGE(K1:K5)
etc, etc
When H1, H2, H3, etc is 0, it generates a #DIV/0! (since you cannot divide by 0)
I want a formula that I everytime a H1, H2, H3, etc is 0, I want to set it to a specific number H1, H2, H3, etc to 15.00
For example:
IF (H1 = 0, 15.00, sum(E1,F1) / H1)
If the number provided (H1) is 0, set it to 15.00, otherwise add the two numbers (E1, F1), and divide this result by the number provided (H1)
But this is where the problem is:
In every single cell that I have NOT yet provided information, it treats the BLANK cells as if it was 0, and has set every single cell to 15.00.
View 9 Replies
View Related
Dec 21, 2007
i want to divide eg. >53/>12 but i get #value! error. i have huge columns full of is so i dont wanna so 53/12 kinda thing 1000 times. whats the formula i need to use?
View 6 Replies
View Related
Jun 22, 2012
How could you divide all numbers in a pivot table by a certain number?
View 1 Replies
View Related
Jun 19, 2008
Imagine I have cells A1 to A5 with a sequence of "numbers" like this:
A1 = 50-55
A2 = 45-67
A3 = 56-90
A4 = 49-80
A5 = 51-55
The left hand side numbers represent a score a minus sign (-) to separate and the right hand side represent another score. I want a formula that I can add, subtract, divide, average, etc, etc these numbers.
If I want to add the left hand scores, for example, cells A1 to A3, sum would be 151 (50 + 45 + 56).
View 9 Replies
View Related
Jun 30, 2009
I have a 52 (one for each week) page workbook. I am trying to average cell J9 for the entire 52 pages. However the information is not added to the cell until the end of the week so week 30-52 all have 0s and should not be counted in the dividing number. Is there a way to have Excel count the number of cells that have a number (not 0) and divide the sum of the cells but that number.
=(WK1!J9+.....WK52!J9) / (counted number of cells not = 0)
View 14 Replies
View Related
Sep 1, 2008
I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.
I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.
If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.
For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.
View 12 Replies
View Related
May 6, 2009
1 35
2 37
3 39
4 22
5 25
6 27
7 28
8 23
9 25
I have the above table. What I want excel to do is to grab the largest number in each set of numbers from the second column and return the corresponding number in the first column. So basically, excel would return a 3, 7, and 9 in cells C1, C2, and C3.
View 8 Replies
View Related
Jun 24, 2009
how would I go about pulling the three sets of numbers out of the following example:
Detected 2 resets. Imported 16 out of 22 bookmarks.
These sets of numbers could be single, double, or triple digits. I need to find the percentage from the second and third set of numbers in the long run.
View 7 Replies
View Related
Dec 15, 2009
My spreadsheet is multiple rows and columns of sales data with two blank rows between salespeople. Ex:
$100 ABC Inc. John #101
$150 XYZ Inc. John #101
$200 Golf Inc. Sue #102
$150 BBall Inc. Sue #102
What I need is this:
$100 ABC Inc. John #101
$150 XYZ Inc. John #101
$250
$200 Golf Inc. Sue #102
$150 BBall Inc. Sue #102
$350
Where the sum of each salesperson's sales figures is shown and formatted. I've been doing this manually. I'm having difficulty figuring out how to do this using VBA. I can do the formatting, and conceptually I see what needs to be done.
View 2 Replies
View Related
Jun 25, 2012
I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.
I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.
View 2 Replies
View Related
May 1, 2013
I have two sets of data:
Data set #1
Indicator PriceIndicator Price Date
2.1 10/27/08
2.11 10/22/08
2.17 11/21/08
2.38 03/20/09
2.38 03/25/09
2.46 03/19/09
2.5 03/09/09
2.5 03/24/09
2.53 12/04/08
2.73 12/09/08
2.82 12/24/08
2.83 12/18/08
2.89 12/12/08
2.9 03/13/09
Data set #2:
Close Price Close Price Date s1s2s3s4r1r2r3r4
2.25 12/11/09
2.30 12/12/09
2.40 12/13/09
2.00 12/14/09
2.12 12/15/09
2.50 12/16/09
2.51 12/17/09
2.53 12/18/09
2.49 12/19/09
What I'm attempting to do is find what are the CLOSEST four indicator prices in data set #1 are above (r1, r2, r3, r4) and below (s1, s2, s3, s4) the closing price in data set #2. Also, I can only use the indicator numbers in data set #1 that are on or before the close price date in data set #2. Because of this, not all of the r's and s's will be filled in.
I would like to use just regular excel formulas, but I have a feeling that VBA may be my only option.
View 3 Replies
View Related
Jun 1, 2012
vb code that will break out each 6 digit media used in their own rows such as in the illustration below.
Sample data before macro
Master Backup
GTI - Hostname
[Code]....
View 2 Replies
View Related
Apr 10, 2008
Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 Replies
View Related
Apr 8, 2014
I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.
I even tried putting the formula in another cell and then referencing to that cell, still didn't work...
The "divide by zero error won't trigger the conditional either......
[Code] ....
View 3 Replies
View Related
Oct 10, 2008
If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value.
Currently for example in cell F5 I have a value of 41 that I need to break down so
I have done the following:
In Cell P5 =IF(F5>=25,F5-21,F5)
Then in Cell Z5 =IF(P5>=25,P5-21,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.
View 3 Replies
View Related
Aug 20, 2009
I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.
She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.
How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?
View 12 Replies
View Related
Feb 2, 2014
I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.
View 3 Replies
View Related
May 21, 2009
If CheckBox1.Value = True Then
Range("e25") = ComboBox6.Value
ElseIf CheckBox2.Value = True Then
Range("e25") = ComboBox6.Value / 2
End If
Im getting an error on the second to last row, Ive never tried to divide using VBA before and I have no idea how to!
Also the amount im trying to divide will always be in the format hh:mm
View 9 Replies
View Related
Oct 16, 2006
I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?
View 6 Replies
View Related
Apr 7, 2014
I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.
format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status
I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.
How do I do it without manual labor?
View 1 Replies
View Related
Aug 1, 2014
I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 3 Replies
View Related
Aug 1, 2014
I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 10 Replies
View Related
Jun 15, 2009
Excel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 11-20%, 21-30%, . . . bottom 10%)?
View 2 Replies
View Related
Jun 20, 2014
I have a data extract that gets generated at the end of each month that I post into the 'Full Extract' tab on my example spreadsheet.
writing a macro that will divide the various items in the 'Extract' based on the Reference, (some items may need to go into multiple sheets)
then draw down the formulas that need to be applied to the item.
View 2 Replies
View Related
Dec 16, 2009
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
View 9 Replies
View Related
Sep 6, 2005
=1/25%
is it that you want a UDF
Function MyFun(a,b)
MyFun = a / b
End Function
Usage:
=MyFun(1, 25%)
Mangesh
"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...
View 14 Replies
View Related
May 11, 2009
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:
4+4/636 = 79.5
d2+f2 (divided by) h2 = 79.5
View 3 Replies
View Related
Aug 27, 2009
I really struggle with the following scenario:
A1=5 B1=4 C1=0 D1=0 E1=5
What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0).
In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?
View 3 Replies
View Related
