Maxif Function In Vba

Nov 10, 2004

I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:

= SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?

Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)

AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))"

End Function

View 5 Replies


ADVERTISEMENT

MaxIf Function

Oct 20, 2009

I would like to write a formula that looks at the last month with a percent value greater than zero, then return the percent value in a specific cell. In the example below I want to display 85.0% in the cell. It should update each time data is entered for the following month.

Here is the source:

MonthPercentage
October 96.0%
November 2.0%
December 70.0%
January 85.0%
February 0.0%
March 0.0%
April 0.0%
May 0.0%
June 0.0%
July 0.0%
August 0.0%
September 0.0%

View 9 Replies View Related

"MAXIF" Function, Or Equivalent?

Dec 1, 2008

I have a spreadsheet that I'm working on that compiles survey data from an online survey. I have averages, high scores, low scores, etc. figuring off of my data to product charts and graphs for a client. I am attempting to find the high and low scores for individual surveys (there are 1054 surveys total right now). I know of max and min, but I really need something that would function like a "maxif" (which I realize does not exist).

Here's the problem:

In column A, I have a list of insurance provider names per survey (so, 1054 entries total). I then have some columns in between that show scores on questions from the survey, and then column AB contains my averages for each individual survey. Column AD has a list of the insurance provider names in alphabetical order (there are 167 unique provider names used in the 1054 surveys). Column AF is my high score column and lines up with column AD (so, I am looking to have 167 high scores in total as I want the high score per each provider, not survey). Let's pretend A2:A10 say "Anthem Insurance." I want to create a formula that would basically say something like:

Count what rows in column A = "Anthem Insurance," then match those cells to AB (so, it would figure since A2:A10 are the targeted cells, then I also want to correspond to AB2:AB10) and give me the high score (MAX) for that range of cells. Is there a way to do this? Otherwise, I have to manually go through all 1054 rows and see what range of cells equal a certain insurance provider name to get those 167 high scores. I can keep doing this if I have to, but this data changes every single week and it eats up a lot of time.

View 4 Replies View Related

MAXIF And MINIF Functions With Wildcards

Jul 16, 2009

I have a list of bank transactions in a spreadsheet

for example....

1/7/09 Sainsburys Petrol 34856385
4/7/09 Paypal 92017465
6/7/09 Sainsburys S/Market 38264059
8/7/09 Halfords 93862830
11/7/09 Sainsburys S/Market 34957372
13/7/09 Paypal 92037454
14/7/09 Barclays BCC 173256

and wish to use formulae similar to MAXIF and MINIF to find the last date and the first date of Paypal transactions (for example) if I have just the word "Paypal" in cell D1

I've tried
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1)*$A$1:$A$7))

but obviously this is looking for exact match of "Paypal"

I have successfully used a wildcard in SUMIF functions elsewhere...

eg, $D$1 & "*"

but for some reason
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1 & "*")*$A$1:$A$7))
does not work

Does anyone have any suggestions?

View 12 Replies View Related

MaxIf Across Discontinuous Range - VBA Or Formula

Sep 6, 2013

I want to create VBA code, or a formula that achieves MaxIf across a Discontinuous Range.

I already have the basic Max If array formula which works in another place:

{=MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))}

Where each named range is a column of 20 continuous cells.

This actually sits within a larger formula {=IF(MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))=0,EditAsset_DatePurchased+(EditAsset_MaintenanceCycle*30.4167),MAX(IF(EditAsset_M aintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))+(EditAsset_MaintenanceCycle*30.4167))}

Now I need to achieve a similar thing, but in a master list of all the data which necessitates a different layout so the data can't be in 2 columns, and can't be continuous. So:

Max the dates in cell BE7, BI7, BM7, BQ7, BU7, BY7, CC7, CG7, CK7, CO7, CS7, CW7, DA7, DE7, DI7, DM7, DQ7, DU7, DY7, EC7...

...IF the value of the corresponding cell two columns to the left of each is "Scheduled Maintenance",

e.g. cells BC7, BG7, BK7, BO7, BS7, BW7, CA7, CE7, CI7, CM7, CQ7, CU7, CY7, DC7, DG7, DK7, DO7, DS7, DW7, EA7

I tried creating 2 named ranges (one for the cells in each bullet point above) and using that in the array formula, but that didn't work and as this formula needs to go down 8,000 rows it would require 16,000 named ranges which isn't really practical.

Ideally I think I would perform this calculation in VBA and just output the answer to the relevant cell. [The VBA code will be triggered via a Worksheet_Change event when one of the date cells, or the 'maintenance cycle' cell elsewhere is changed].

I think this could be done by taking the values from the cells above into two arrays, and then 'max if' those arrays within VB somehow - but I don't know how to create the arrays or then perform 'max if' within VBA.

Alternatively a formula would suffice - I could insert this formula into the output cell via the VBA Worksheet_Change event when an input cell (date or maintenance cycle) is changed, and then have the same VBA code copy this cell and paste the value - can't have up to 8,000 array formulas live in my sheet for performance reasons! But I don't think this will be easily possible via a formula and VBA would be cleaner.

View 7 Replies View Related

Find Address Of MaxIF In A Range

Apr 4, 2007

I am having troubles with using two methods together. I know how to find the address of the first or last occurance of a MAX in a range. I also know how to create a MAXIF function using criteria to determine what to consider for the max calculation. I do not know how to return the address of the result from the MAXIF.

I am using Office 2003 on WinXP.

Ultimately I want to take the address of the first result of a MAXIF and use it to do a lookup for a corresponding value. I'm not sure if I will be able to do this with a combination of functions, or if I will need a macro.

Heres an example:

Data:
Time WeekNum Count
01:02 1 12
02:05 1 35
03:09 2 456
14:20 2 123
12:24 2 122
Usage:

Use the maxif lookup to return the time of the largest count for WeekNum 2

Result:
03:09

View 9 Replies View Related

Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

Apr 8, 2014

I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:

[Code] ........

When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.

View 14 Replies View Related

Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies View Related

Function Returns Value To Cell As Single - Function Is Defined To Return Double

May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double

[Code]....

View 9 Replies View Related

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

Calling Function That Acts On Matrix Range Within Another Function

Jul 23, 2013

I have a function

VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function

And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:

VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function

I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?

View 2 Replies View Related

Nesting Index / Match Function Within Vlookup Function?

Dec 3, 2013

let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.

[Code]....

This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.

I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:

This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0

This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered

Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0

Index: array = $BA$434:$DN$452

So I think my final function is

[Code] .....

But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.

View 2 Replies View Related

Function To Mirroring Cell Relative To Function's Location

Jul 17, 2008

I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

View 13 Replies View Related

Nested IF Function Error (entered Too Many Arguments For This Function)

Nov 25, 2008

I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

View 6 Replies View Related

Nesting Ceiling Function In IF Function - Conditional Formatting?

May 13, 2013

I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)

right now it looks like:

If (C10

View 1 Replies View Related

Force Function To Use Row Relative To Cell Housing Function

Mar 2, 2008

I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

View 9 Replies View Related

2007 Right Function With Embeded Find Function

Mar 26, 2009

I have a range of cells, for this example I will use 2.

Cell E17 = 77/170
Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....

View 2 Replies View Related

Concatif Function Breaks Beforedoubleclick Function Just By Being There?

Nov 19, 2009

I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

View 5 Replies View Related

Scoping Function As Public - But Not Available As Worksheet Function

Sep 24, 2012

I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".

However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"

Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?

View 5 Replies View Related

Show Insert Function Dialog Pre Set To Function

Sep 14, 2006

A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

View 4 Replies View Related

User Defined Function Function Tooltip

Oct 9, 2007

when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

View 9 Replies View Related

Write The VBA Function Through The Function Wizard

Sep 16, 2009

I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

View 2 Replies View Related

Vlookup Function Together With An Offset Function

Oct 26, 2008

I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

View 8 Replies View Related

Work Around To The 7 Function Limit With The IF Function

Nov 11, 2008

I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).

I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.

View 3 Replies View Related

COUNTA Function With Extra Function?

Dec 23, 2011

I am using the COUNTA function which works great but I also have data that I dont want counted and do not know of the problem solver.

E.G
in B1 I have vlookup function thats brings back either "B", "C" or "M".
in c1 I have =IF(A1="C",B1,"")

Now, that tells me if the data is B or not. If it is, it says B If it isnt, its blank

Thats great but when im trying to count how many "B"'s there are, it counts the blanks as well!

View 4 Replies View Related

COUNTIF Function After SUBSTITUTE Function

Jan 3, 2012

I am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.

Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?

View 9 Replies View Related

If One Function Returns Error Then Use Second Function

Feb 18, 2013

I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.

View 5 Replies View Related

IF Function - Can Combine AND / OR And Creating (at Least) Function

Jun 24, 2013

I have the following function:

=IF(AND(I17>=$I$1,O17>=$O$1,Q17>=$Q$1,F17

View 2 Replies View Related

AVERAGE Function Embedded Within An IF Function

Feb 18, 2010

TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

View 9 Replies View Related

Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column

Oct 13, 2009

On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

View 5 Replies View Related







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