Sumproduct :: Forecasting Purposes

Oct 19, 2009

I am trying to come up with the appropriate formula that can be used for possible forecasting purposes. Countif proves to be futile due to more than one criteria that needs to be met. Sumproduct seems to be more feasible but I am unable to get the correct formula or maybe I am wrong.

Here's the objective...I need to be able to plot the total number of people working on a project on a daily basis - # of people working on a Monday, Tuesday and so forth by means of referring to start and end date column..I am open to revisions so as to streamline everything.

View 5 Replies


VBA - Autoname Cell For Hyperlink Purposes

Nov 19, 2011

having an excel file autoname cells in order that I can hyperlink a picture to each tab individually.

Example - The file has autonamed tabs Wednesday 1, Tuesday 2....etc. In each tab is a reservation guide for three restaurants and the logo's at the top of the tab should hyperlink to a cell within the same tab. Blu Restaurant = A32, Grille = A223 within the same tab. How can I do this without manually naming the 3 cell's in each tab? I will also need this to be flexible so that when I create the January worksheet it populates from the tab names that are created using VBA already.

View 2 Replies View Related

Sorting Pre 1752 Years For Genealogy Purposes

Apr 29, 2013

I am trying to sort by years (I have 66230 records to sort on) Parish Records that date from 1538/1659. The year as we know it now - did not come into existence until 1752 - and i have been trying to sort my data on the "years". I know i have done this before - however, i have either forgotten how to do it - or the Excel 2010 was not the version i accomplished this on.

Basically i have this:-

25 Sep 1596/97
25 Jun 1596
05 May 1611
10 Feb 1611/12

When i do a normal "sort" the split year comes before the main year as in 1596/97 comes before 1596 - thus putting all the dates out of sequence.

how to over come - i am no good with macro's - so if it can be done with using sort - it would be easier for me.

View 8 Replies View Related

Add Sum Of Lbs And Ounces For Shipping / Packing List Purposes?

May 16, 2013

I am looking to sum the weight I add in my packing list spreadsheet.

below are two ways i have been working with and will settle with what ever i can get. all i need is to be able to enter the weight of a package either by lbs.oz or in separate columns (lbs)(oz) then have a formula take either way and know there is only 16 oz in a lb and give me a total weight of all things shipped.



View 1 Replies View Related

Automate Pop-up Error Message For Data Validation Purposes

Jul 18, 2014

Excel spreadsheet, that contains several columns. Three of these columns contain text data that is selected by using a simple drop-down menu sourced from a list. If an attempt is made to enter text data not in the associated list an error message is displayed. Easy so far.

I need need to extend the text data validation across all three columns so that only specific combinations of the text in the three columns can be used which is reflected in a further list. These combinations will be counted elsewhere and so needed. Therefore, if i attempt to enter a combination of text columns that is not reflected in the list, this too will present an error message stating 'combination not valid', or words to this effect.

View 2 Replies View Related

Projection Spread Sheet For Cash Flow Purposes

Jun 2, 2009

I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.

A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...

I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.

View 9 Replies View Related

Forecasting Setup

Sep 14, 2006

If I have a database of 1000 records that are logged and counted as to which of these records are successful and unsuccessful sales,

can excel be programmed to give an accurate forecast of what will be the number/ percentage of successful sales records over the next 100, 50, 30 and 10 new record entry ranges, using the database history?, then which of the new records within each selected range are most likely to be successful sales using the database history?

If so, what components or data references are needed in a worksheet to meet these requirements to allow for creating an accurate forecasting application within excel?

View 9 Replies View Related

Offset And CountA To Auto Select Data Range For Charting Purposes?

Dec 12, 2013

how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).

So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.

Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.

I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?

View 8 Replies View Related

Seasonal Forecasting Trend

Apr 2, 2009

I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.

I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.

Here's a sample of the data....

View 12 Replies View Related

Calculating Total Forecasting

Jul 15, 2009

I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.

For example I have two months and I want to know how to calculate the forecasted Jun 09 total: ...

View 10 Replies View Related

How To Work With Forecasting Templates

Dec 27, 2013

I do forecasting for my company. We have a forecast every quarter. Any good templates (simple or complex) for presenting different quarters (or time periods) and the actual results for management?

View 5 Replies View Related

Forecasting Based On Two Years Of Data

Nov 21, 2011

Have to forecast/create a trend for the current year based on the data available for last two years. Below is the format of data. The current year data is kept blank as the data needs to be filled based on forecasting/ trending.


View 3 Replies View Related

Assistance With Budget Expenditure Forecasting

Jun 19, 2008

I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).

I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2

View 9 Replies View Related

Forecasting Multiple Store Openings

Dec 8, 2008

I have a base model for site openings and opening schedule. I need to build a 5 yr model which reflects the base model for each opening. I have matched the month in the model to the opening month but how do I get the base model subsequent months to follow the opening month?

View 10 Replies View Related

VBA - Macro Run When System Clock Changes - Forecasting With Live Feed?

Sep 21, 2013

I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...

How to modify the code a bit...

Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown

I tried to change the code, but it did not worked.

View 8 Replies View Related

Forecasting Using Historical Seasonally Effected Non-linear Data

Jan 15, 2010

Forecasting using historical seasonally effected data

I organize my thoughts in numbers so it will be easier to understand.

[1] Say I have this data from January 2009 to December 2009:


As you can see, the growth is not linear.

[2] So then, I start with this data point for Janaury 2010.


[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).

[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:


So how do i do this now?

View 9 Replies View Related

SEASONALITY Based On Names Of Months In Forecasting Model

Jul 16, 2007

I have historical sales for each month from the last 3 years. I would like to set up a seasonality adjustment for forecasting. I know that it won't be perfect and some may even suggest using regression, but I'd rather not. I'd like to do the following:

In a control sheet, list the months in chronological order beginning in cell A1; January, A2, February; etc., etc. through December.

Next, below each month, I'd like to have a factor. For example, in B1 beneath January, I'd like to be able to plug in 75%. This would say that for each January going forward, that it is 75% of the annual historical average for all periods. Whereas, say in June, it is 140%.

View 9 Replies View Related

Forecasting Purchasing: Setting Up A Spreadsheet That Will Allow Me To Look At Previous Orders And Forecast

Apr 11, 2007

how I would go about setting up a spreadsheet that will allow me to look at previous orders and forecast what I need to buy. He never got into trouble for ordering too much, but he did get caught on ordering too little. I am thinking something along the lines of the attached. However, if anyone is using something that looks or works better (or those who solve this little problem have done a better looking or working sheet) let me know.

There will be some Vlookups that need to bring in description and price (or some other function if there is a better way to do it). The min need and max need will be populated and brought in by vlookup. The order column should be where the forecasting happens based on previous orders. I am willing to have all the data in the same workbook. The second or third sheet can be used to store data in what ever format that is needed to be done.

View 8 Replies View Related

Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and

SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and

SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

View 10 Replies View Related


Jul 11, 2009

I have the following formula:

View 4 Replies View Related


Mar 25, 2009

I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.

I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....

View 3 Replies View Related

How To Add Another Sum To Sumproduct

Jul 11, 2012

How to add another sum to this formula?

this is the formula i have at the moment


View 4 Replies View Related

Using SumProduct

Dec 4, 2007

Here's my question.
jan- feb- mar- apr- may
rep a 1 1 2 5 1
rep b 1 2 7 7 7
rep c 5 5 1 2 2
rep d 1 1 1 1 1
rep e 2 2 2 2 2
rep f 1 2 3 4 5
rep g 0 0 0 7 9

date date
March May

How do I set it up so that I will have two cells that will contain a month. Once the 2 months are selected it will give me the total for each rep during that time period? Do I use sumproduct?

View 9 Replies View Related


Aug 12, 2008

I have a report with a macro that imports a .TXT file into Excel then calculates the follwing formula. For some reason, before the .TXT file is loaded the formula is fine and has no errors, then when it is run I get a "#NAME?" error. I thought it might be because the formula changed during the macro but that's not it. Examples are below.

I've looked online and tried naming the ranges, but that doesn't work. I've tried the built in help but that didn't help. I've tried making sure the analysis pak is installed but that's not it either.

I've ran out of ideas now and you guys are my last hope. Any clues...??

Before macro:

After macro:

View 9 Replies View Related


Aug 13, 2008


it is if the cell in Column T >500,000 or the cell in Column U is more than 500,000. I keep getting a value error and I can't find a way around it.

View 9 Replies View Related

Sumproduct With Min/Max ...

Aug 26, 2008

I'm working on a project on predicting future cash flows from loans we have made and have ran into a wall as i can't figure out the formula to achieve what i would like to do. I've gotten a lot of help from you guys in the past and would appreciate any help you may be able to provide for this problem. So here it goes. First, here is how i have the spreadsheet setup.....

View 9 Replies View Related

Sumproduct In VBA Getting #Value

Nov 12, 2008

I've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my
Function weight(Aref As Range, count As Integer, Bref As Range) As Variant

Dim i As Integer
Dim top As Integer
Dim vA() As Variant
Dim cA() As Variant
Dim vR As Range
Dim cR As Range

Dim count As Integer

count = Widgets.Rows.count

ReDim vA(count - 1)
ReDim cA(count - 1)

top = Aref.Rows.count

For i = 0 To top - 1.............................

View 9 Replies View Related

Sumproduct :: Get Sum Of 0

Apr 29, 2009

I seem to get a sum of 0 when I use this sumproduct syntax: SUMPRODUCT(($R$77:$R$442="H")*($Q$77:$Q$442="OTE"),(D77:D442)). What am I not doing?

Below is an e.g. of my data


View 9 Replies View Related

SUMPRODUCT Getting #Value

Jul 27, 2009


This problem is I keep getting a #Value! error when I place the date section into formula. I have check to see thatthe date format is correct and as far as I can tell it is EXAMPLE: 06/12/2007

This formula did work in a nother worksheet, thus the confusion Ihave

The other issue is how to get two different text items in the "HOW" group to be True for Formula to pull requested values???

View 9 Replies View Related

Sumproduct In VBA

Apr 30, 2003

Now I am trying to build a user-defined function to use it.
It works in EXCEL but I can't get it to work in VBA

Function xxx(x,y)
Dim a, b, c As Range
a = Range("a4:a14") ' contains text
b = Range("b4:b14") ' contains text
c = Range("c4:c14") ' contains values
Range("a20") = Application.SumProduct((a = x) * (b = y) * c)
End Function

View 9 Replies View Related

Copyrights 2005-15, All rights reserved