Define A Range For SumProduct Function Based On An If Statement?

May 31, 2006

I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?

View 4 Replies


ADVERTISEMENT

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View 9 Replies View Related

IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)

Dec 26, 2008

I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.

=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)

View 5 Replies View Related

Define Range With Address Function

Dec 26, 2006

I am trying to create a correlation matrix that uses the =ADDRESS function to define the various columns of data which I want to correlate against each other (I require this flexibility because the number of columns and rows of data to be correlated will change). The basic formula I am using is something like the following: =CORREL((ADDRESS(Sheet1!$C$2+3,L2+2,,,"Sheet1")),(ADDRESS(Sheet1!$B$2,L2+2,,,"Sheet1")))

However, while the individual ADDRESS fomulas deliver the correct start and end cell references required, it seems that CORREL function won't accept the cell addresses that result from using the =ADDRESS function.

View 5 Replies View Related

Define Percentage Based On Value Range?

Nov 27, 2013

I need to be able to work out a percentage based on a value range. ie.

Cell D2 to D5 will define the percentages (so I can adjust it and play)

D2 = 18%
D3 = 15%
D4 = 13%
D5 = 10%

I need to work out:

If the value in cell A10 is between 11 and 200 calculate on D2, if between 201 - 500 calculate on D3, if between 501 - 2000 calculate on D4 ect ect

Cells A10 to A100 will have some figures put in them.I then want to calculate the %value used based on weather the figure in one of the A10 to A100 cells falls in a range. The data being put into the cells in column A will vary from report to report.

I'm trying to automate the calculations :

View 6 Replies View Related

Define Range Based On Named Row And Column

Jul 26, 2006

I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right.

Sub LastRowCol()
Dim intLastRow As Integer
Dim intLastCol As Integer
Dim ws As Worksheet

For Each ws In Worksheets

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column

View 3 Replies View Related

Define Range Object Based On Cells Not Null

May 20, 2006

The problem that I am having today is defining a range object that cannot include null cells. If it does include Null cells then the filterwill fail. the cells that I need to define are all in a cohesive unit. the other thing to know is that the cells that are not null will never be mixed in with cells that are null. so for instance you might have a range of cells from one to 100, the first 50 might be full. the last 50 would all be null. in that situation I would need to loop through those cells to define a range object that would just see the first 50 cells ....

View 6 Replies View Related

Possible To Define Range For Combobox Input Based On Number Of Non-blank Cells?

Nov 25, 2013

Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.

Code:

Worksheets("Sheet1").Shapes("Combobox2").ControlFormat.ListFillRange = _
"O2:O14"

View 3 Replies View Related

Countif Or Sumproduct Function (count In A Range)

May 6, 2009

I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.

View 2 Replies View Related

Changing Range Of Sumproduct Function Depending On Length Of Data Series

Feb 20, 2007

I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?

View 2 Replies View Related

Sumproduct Statement

Aug 21, 2008

I'm just wondering if I can expand on the following sumproduct formula:

=SUMPRODUCT(--($B$2='21_Jul08_NTH'!$H$2:$H$582),--($K$1='21_Jul08_NTH'!$O$2:$O$582),--(Misc_Inputs!$B$3='21_Jul08_NTH'!$L$2:$L$582),--(D7='21_Jul08_NTH'!$B$2:$B$582),--'21_Jul08_NTH'!$J$2:$J$582)*-1

I also want to add another criteria to say that only bring in data that equals the following "trade id's":
5578
5550
3437
3443
3440

At the moment I believe the sumproduct can only filter based on 1 criteria at a time and not an array of criterias?

View 9 Replies View Related

Define Merged Cells - Before Save Function

Oct 19, 2008

I used this forum to successfully prevent a user from saving a workbook without entering data in a certain cell. However, I now have a merged cell that I must refer to when writing my "before save" code and don't know how to refer to that cell. Below is what I have: One workbook with nine worksheets. The merged cells I want to refer to are H11 and I11 in sheet seven. User RYLO posted the below code that worked for me but I don't know how to alter it to refer to a merged cell.

View 4 Replies View Related

Define Argument Prompt On Function Module

May 6, 2009

I have made my own excel function that my users needs every day. it works just fine, however if you do not know the required argument for this function, you couldn't use it. How can i prompt my users for the right order of the argument when they type the function in excel?. example is when you type the Function "=left(" you will be prompted by excel of the correct arguments.

View 3 Replies View Related

User Define Function To Count Bold Cells

Oct 16, 2007

I currently have this macro that helps me SUM all the cells that are bold.


Public Function SumBold(Rng As Range)

Dim Total As Double
Dim Cell As Object

Application.Volatile
For Each Cell In Rng
If Cell.Font.Bold Then Total = Total + Cell.Value
Next
SumBold = Total

End Function

I want to have the same kind of Macro, but that could COUNT the number of cells that are Bold.

View 9 Replies View Related

Adding Another Condition To Sumproduct (countif) Statement

Oct 6, 2011

I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.

What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.

So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.

SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))

View 5 Replies View Related

Define Range() In Vba

Jan 23, 2007

I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?

View 2 Replies View Related

Define A Time Range

Jan 21, 2010

I have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.

The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.

View 8 Replies View Related

Define Range As Variable?

Feb 23, 2013

why this won't work.

Code:

Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange

View 6 Replies View Related

Using Variable To Define End Of Range

May 19, 2008

how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:


Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next

I'd like to define the end of the range with more accuracy than I'm currently doing.

View 9 Replies View Related

Define Range During Macro

Apr 25, 2006

I'm trying to define a range to be copied during execution of a macro. The range to be copied must include all contiguous data from a defined starting range to the rightmost column and the bottommost row containing data (boht of which can change depending on input data). I use <End>+<RightArrow> and <End>+DownArrow> to define the range when working in Excel (not under macro control).

I used the macro recorder to define the code for the process described above, with results shown below:

Sub Macro2()
Sheets("Source_Info").Select
Range("A2:D6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working_Data").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

When I run the macro, however, the End(xlToRight) and End(xlDown) commands do not have any effect (i.e., the rang is not made any bigger when these statements are executed).

I've used the same code in previous macros (with previous versions of Excel and Windows), and it worked fine. I'm running Excel 2003 under Windows XP Professional now.

View 5 Replies View Related

Define An Unknown Range

Aug 13, 2006

is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown

View 9 Replies View Related

Define A Variable Range

Dec 22, 2006

I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:

Range("A1268:E1242").Select
Range("A1268:E1242").Activate

Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0

Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub

View 8 Replies View Related

Define Named Range

Feb 7, 2007

how to set up an array within a worksheet

View 3 Replies View Related

Define Range With Numbers Only

Mar 26, 2008

how do you go about referencing a cell by using a value of an integer? For example, I want to select cell A5. I have the number i= 5 (in my vba code) and I want to use that together with the row letter A.

View 4 Replies View Related

Cell Value To Define VLOOKUP Range?

Feb 26, 2014

is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.

eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".

I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,NAMED RANGE,2,false) 

the Named range would be a value in the same row ie B4 - being Male or Female

so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  =vlookup(D4,B4.Value,2,false) 

otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).

Many more Excel Formulas  Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) ) 

View 2 Replies View Related

Define Range By Selected Cells

Jul 19, 2014

I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.

[Code] ........

Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'

'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.

View 6 Replies View Related

How Can I Define A Range, Only For The Active Cell?

Jan 27, 2009

How can I define a range, only for the active cell?

View 6 Replies View Related

Define Range Start And End Using Comboboxes

Jan 25, 2012

I have a daily collection of data based on half hour meter readings. Responsibility for this data is to be split between core and non-core hours so for example core hours would be from 6:00 to 18:30.

What i would like to do is have a combobox for both the start time and end time allowing the user to change these as appropriate.

Where i'm struggling is using these comboboxes to select the data between these two times and total the values per row.

My data is currently arranged with the times (staring at 00:00) running horizontally on row 14.

View 9 Replies View Related

How To Define A Cell To Be A Range Dynamically

Feb 14, 2013

The way I have try to define lastcell is wrong. But I don't know how to do it.

Code:
Sub createUniqueRecordID_2()
'
' fill using CTRL + ENTER
'
Dim lastcell
Sheets("projects list").Select
[a1].Select

[Code]...

View 1 Replies View Related

VBA To Define A Range - Worksheet Name With Spaces

Apr 30, 2013

I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.

Code:
Sub AddDynamicRangeVertical()
On Error Resume Next
Dim sRangeName As String
Dim n As Name

If ActiveWorkbook Is Nothing Then Exit Sub

[Code] ..........

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved