# Differencing Sets Of Numbers, Multiplying, And Summing

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
ADVERTISEMENT
May 6, 2008

What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?

View 4 Replies
View Related
Jan 21, 2010

I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).

In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.

Is there a simple way/formula of doing this?

View 13 Replies
View Related
Mar 3, 2014

I run a report from a scheduling software that gives me the total hours and minutes for a weekday for my bus routes. For instance, 253 hours and 23 minutes for one day. Now though, I need to calculate how many hours and minutes for the entire month. The report will only come out with 253h23. I need to somehow take that, turn it into time and then multiply it by 20 (January weekdays). But I need real hours and minutes and I'm having trouble getting them.

View 7 Replies
View Related
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
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
Dec 23, 2011

I have a problem in terms of summing unique numbers in a set of reoccurring numbers. Due to the restrictions in the spreadsheet I am unable to use a filter / advanced filter or pivot table. What I basically want to do is sum every time there is a different number. In this case it's:

37.85+80.61+129.38+185.85+1259.69+104.96

The unique numbers have a maximum of 6 and a minimum of 3. Is there a generic formula that I can apply so that it only counts / sums the number once. I can use VBA code or just a simple array. I'm just not familiar with any sort of functions that can do this.

37.9537.9537.9537.9580.6180.6180.6180.61129.38129.38129.38129.38185.85185.85185.85185.851259.691259

.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259

.691259.691259.691259.691259.69104.96104.96104.96104.96104.960.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00

View 9 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
May 14, 2007

I have a row of numbers that I want to sum. However some of the data could not be retrieved and that means in some cells in the row of numbers contain the message: " #VALUE " and when doing SUM it returns #VALUE

View 4 Replies
View Related
Feb 23, 2014

I have a process that generates the value X in cell A1 randomly. So I can change the value of X in A1 by clicking on F9. I want to sum a sequence of X values. I can do this by manually copying each X to a row of cells and then summing the row values. But that is tedious. Can the summation be done automatically without being regarded as being circuler?

View 8 Replies
View Related
Jun 3, 2014

I want to sume numbers that may or may not be in several cells. If I use the function SUM and one of them is missing, I get an error.

Example:

A1=2

A2="empty"

A3=5

I want the sum of A1+A2+A3 to be 5

View 6 Replies
View Related
Apr 10, 2014

I have a column of numbers (each cell represents number of chicken eggs produced daily). Column data runs from K9:K415

I want a formula in column M starting at M9 whereby cell M9 sums up the total number of eggs for the week (i.e. sum of K9:K15), then I want to copy that formula down, such that M10 shows the sum total of eggs for the next week (which is sum of K16:K22) and so on. Thus summing up every 7 rows of data in column M.

I have been playing with the SUM and OFFSET functions but can't quite nail it.

View 1 Replies
View Related
Nov 15, 2008

The series 1,3,6,10,15,21,28,36 ("A" series) is the ongoing sum of 1+2+3+4+5+6+7+8 ("integers") and naturally goes on for as long as on chooses. I am only interested in the numbers up to 100.

Is there an Excel function that will allow me choose any integer as a starting point and any higher integer as an ending point and provide the sum of the "A" series associated with the two choices.

For example: If I begin at 6 and end on 12 it would give me 308 =28+36+45+55+66+78

00

11

23

36

410

515

621

728

836

945

1055

1166

1278

1391

View 9 Replies
View Related
Dec 21, 2008

I have a list of 100 songs and the artists that sing each song.

What I want is a formula that will look at the rank that it finishes with and assign it a different number (e.g. the song that finished at number 1, has a value of 100).

As there can be multiple songs by the one artist, I need to add them ALL up and have a total score for each singer/band.

e.g.

1 Powderfinger - My Happiness (100 points)

2 U2 - Beautiful Day (99 points)

3 Powderfinger - My kind of scene (98 points)

4 Wheatus - Teenage Dirtbag (97 points)

5 Coldplay - Yellow (96 points)

So Powderfinger would have 198 points and the other 3 would have 99, 97 and 96.

The band names are in one column and the song names are in the next column.

View 9 Replies
View Related
Jun 16, 2009

I would need to get this next thing working:

H I J K L M N O P

Type No. No.Points Points/RN Points/KR Points/KV Points/ER R/V Total

5 KV 12 4 2 3 4 6 0 8

6 RN 90 18 3 4 5 8 0 26

7 KV 12 4 4 4 5 6 10 13

8 ER

9 KV

10 KR

...and so on, with the following logic

If Type = KV, then P$ = sum(J$,M$,O$), but if it is something else, like RN, then it needs to sum J$,K$,O$

I've tried using the following:

=IF(ISNUMBER(SEARCH("KV";H2));SUM(J2+M2+O2);0;IF(ISNUMBER(SEARCH("ER";H2));SUM(J2+N2+O2));

but only the two first ifs work, which is kind of logical, since the second if is the "value if false" statement. Is there any way to get this to work?

View 9 Replies
View Related
Jul 27, 2012

I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.

First condition:

1 2 3 4 5 6 7 8 9 10

The function would return: 1+2+3+4+5 = 15

Second condition:

0 0 0 4 5 6 7 8 9 10

Function would return: 4 + 5 + 6 + 7 + 8 = 30

Third condition:

0 0 0 0 0 0 0 8 9 10

The function would return: 10 + 9 + 8 = 27

I have had success with:

=B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5)))

(using ctrl shift enter)

BUT it crashes when faced with the third condition.

I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.

View 3 Replies
View Related
Aug 17, 2013

I have an unusual situation which is best explained by way of an attached example.

In summary adding and subtracting four cells containing integer values outputs an unexpected non-integer value, which should in fact be zero.

The situation is best explained by way of example which I attach to this post : Excel Problem.xlsx

Basically, Cell E1 should be zero.

View 4 Replies
View Related
Mar 15, 2014

I added an attachment. Column A is my "week number" and column E is my sales amount.

I'm trying to create a formula that sums the sales amount, only if it corresponds to a certain week number. I know sumif formulas, but can't get them to work well for this. For example, I want it to sum all the sale that happened in week one, then all the ones in week 2, then all the ones in week 3, and so on. There has to be an easier way than doing a sumif for each individual week and manually changing the week number in the sum if formula.

Could there be a character you can use in a formula to make the sumif week number "running?" So you could then just type in the formula once and pull it all the way down for the year, so the formula will work for the every week throughout the year?

View 8 Replies
View Related
Aug 27, 2009

Column A: Start Date

Column B: End Date

Column C: Ethnicity Code (letter and number such as B1)

Column D: Hours

What I need to do is work out how many hours each ethnic group did in a specific month.

View 14 Replies
View Related
Jul 17, 2014

Product Group Arg.Braz.Mex.Peru

Red paint Paint5324

Blue paintPaint5684

WallpaperWallpaper6585

LampsFurniture 4631

ChairsFurniture 1645

Green paintPaint1356

SofasFurniture 3358

SUM(Paint,Argentina)SUM(Paint,Brazil)SUM(Paint,Mexico)SUM(Paint,Peru)

SUM(Wallpaper,Argentina)SUM(Wallpaper,Brazil)SUM(Wallpaper,Mexico)SUM(Wallpaper,Peru)

SUM(Furniture,Argentina)SUM(Furniture,Brazil)SUM(Furniture,Mexico)SUM(Furniture,Peru)

I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.

This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories.

View 4 Replies
View Related
Apr 14, 2013

I have cell A1:A25. each with a number, and the same text "Hour(s)" So cell A1 would be 24 Hour(s) and so forth down to cell A25.

Due to the sheet being large, I can't just insert another column to list the text. I need for cell A26 to sum the numbers ignoring the test in the cells. I searched to forum, but didn't exactly find anything that works correct.

View 9 Replies
View Related
Apr 28, 2014

I do my indoor cricket team stats and I am having some trouble figuring out what formula to use in order to be able to sum a row of numbers where some cells may contain text and numbers or just text.

Please see the attached spreadsheet to understand what I'm talking about.

In the batting tab, when the batter gets out, they lose 5 runs. Outs are noted by St, R, B or C. In terms of runs, the scorers put wides and noballs to the batter on strike which are noted by W or N and this is worth 2 runs. Sometimes the batter on strike will get runs off a W or N so it's noted down as W+2 (which would give 4 runs) etc. So what I need to be able to do in the batting sheet is sum the total runs and count the number of outs.

In the bowling sheet, it's the same story as the batting tab, but I also need to be able to sum the number of extras given and count the number of wickets taken.

[URL].....

View 2 Replies
View Related
May 9, 2014

I am looking for a formula which will sum numbers with less than symbols in front of them (i.e. they are text cells), and then replace the less than symbol again in the summed cell if initially present. The problem is that not every cell contains a < symbol, some of them are just numbers, and not every column contains a < symbol either.

e.g. I am looking for a formula which if entered in row 5 of this example would give these answers.

A

B

C

D

1

<0.001

<0.1

0.2

<0.01

2

<0.0001

0.1

0.2

<0.1

3

<0.0001

<0.2

0.2

<0.1

4

<0.001

<0.1

0.2

<0.01

5

<0.0022

<0.5

0.8

<0.22

View 11 Replies
View Related
Jan 12, 2010

I'm looking to do the following below: column A would have numbers, column B would have words and a few columns over i would have the total of each same word

column A column B

60house

120car

30misc

52.25food

83.33gashouse 180.25

120.25housecar 120

25miscmisc 55

105foodfood 157.25

50.5gasgas 133.83

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
Jun 10, 2009

Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.

I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.

For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.

View 9 Replies
View Related