# RANDBETWEEN Function

Jul 31, 2009

I'm using the RANDBETWEEN(1,15) excel function to fill a range of cells with random numbers between 1 and 15, the problem is that each cell cannot be the same number than any other in that range, how exactly can i accomplish this?

I attached a file for a better example, if you hit F9 you can see that sometimes at least 2 cells share the same number...but I need it to be different on each one, and still be random.

## Randbetween Function To Only Randomate A Number When A Certin Object Is Pressed

Mar 25, 2009

Is it possbile with the randbetween function to only randomate a number when a certin object is pressed? As at the moment, every button i press or drop down data i select it will randomate a number, which i dont want to happen.

## VBA RandBetween

Apr 1, 2009

I have tried

random_result = WorksheetFunction.randbetween(0, 36)
But failed.

Whats the VBA equivalent?

## How To Use Randbetween Formula

Dec 29, 2011

how to use the =randbetween formula

I need random dates from 25 years ago to today

## IF Statement In VB With CODE()*RANDBETWEEN()

Jul 21, 2009

I am trying to use the formula below in a macro to produce the same result, but I am having trouble with the coding.

## File Shows RandBetween

Sep 26, 2008

I am trying to generate random numbers between upper and lower limits (eg 120,140). The randbetween function in the help file is ideal....but when I use it it generates a #Name error.

(I am using Excel 2002 and have the analysis toolpak add in ticked).

## Capturing All Randbetween Values In Different Cells

Oct 2, 2011

I need the solution for the following:

1. Currently I have a randbetween() formula in cell B2.

2. Cell A1 = B2 (Every time the value in B2 changes so does the value in A1)

Instead, I want that every change in value in cell B2 is captured in progressive cells.

For example:

First iteration in cell B2, value is entered in A1.

Second change in value in B2, value is entered in A2, while value in A1 remains unchanged and so on and so forth.

## 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.

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

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

## 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]....

## 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?

## 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?

## 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.

## 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?

## 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")))

## 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

## 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.

## 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)).....

## 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).

## 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?

## 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)

## 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?

## 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.

## 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.

## 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.

## 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!

## 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?

## 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.

## 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

## 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