User Defined Functions

ashishbarot · Aug 27, 2009

Pls pls pls help me for this prob......

I want to create the user defined function in excel in which i will assign the value to a char and after that i should b able to use it in formula.

Eg: for "FL" i will assign 1
for "LF" i will assign 2
for "VV1" i will assign 3
for "VV2" i will assign 4

and so on.......then i want to use this to compare both figures like
i will put the formula like if(VV1 < VV2,true,false) result should b true coz VV1 is having value of 3 and VV2 is having 4.

So basically how we create the custom lists i want to give the priority to my own list and want to use it in formula.

I hope some one from all the masters will help me out in this topic

Waiting for your reply.

ashishbarot · Aug 27, 2009

BUT STILL MY PROB IS NOT SOLVED MR.Hermanito

WHEN I USE HIGHEST OF THIS NAMES

FOR EG: VV1 2 AND VV2 3........IT GIVES ME 3 NOT THE VV2
MAX(VV1,VV2)
RESULT IS 3
I WANT VVS2

Hermanito · Aug 27, 2009

Starting to use all-caps is considered yelling and rude.
I think I wasted enough of my time on you

ashishbarot · Aug 27, 2009

Hey no no man....that all caps came only by mistake my keyboard caps was on thats Y it came....come on there is nothing to feel bad

ashishbarot · Aug 27, 2009

So u mean to say that i have will have to create the function for every value i.e.

basically i have these list

<title>Excel Jeanie HTML</title>
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: right;">1</td> <td>IF</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: right;">2</td> <td>VVS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">3</td> <td>VVS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">4</td> <td>VS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">5</td> <td>VS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right;">6</td> <td>SI1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right;">7</td> <td>SI2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: right;">8</td> <td>SI3</td></tr></tbody></table>
Can u help me out for the same.......i just want to use the max function with these values, so no prob if i cant use '+'

Hermanito · Aug 27, 2009

Okay then; you'll get the benefit of the doubt...

Have you tried the User Defined functions like I showed in the post with code in it?
And first you talked about VV1 and VV2 and now you have all of a sudden a VVS2. You need to be more clear and more precise about what you are trying to do and what results you are expecting...

Hermanito · Aug 27, 2009

ashishbarot said:
basically i have these list

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>IF</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD>VVS1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>VVS2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>VS1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD>VS2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD>SI1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD>SI2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD>SI3</TD></TR></TBODY></TABLE>

If you put it like that, the defined names should be in the first column. The column next to it is only to easily see what each value is named, but the names themself should be defined on the cells with the values.
Also, like already mentioned, IF will not be a valid name, first because there already is a column IF, and second because IF is a reserved keyword.

yytsunamiyy · Aug 27, 2009

Try the following:

Place in a helper sheet called "List of Values" your list like this:

Excel Workbook

A

1

FL

2

L

3

VV1

4

VV2

List of Values

Then, on your actual sheet you can use something like the following:

Excel Workbook

A

B

C

D

1

Compare:

VV1

with:

VV2

2

VV2

Formulas

Cell Formulas
Range	Formula
A2	=IF(MATCH($B$1,'List of Values'!$A$1:$A$4,0)

edit:

For MAX / MIN you can use the following:

Excel Workbook

A

B

C

D

4

Find max of:

VV1

VV2

FL

5

VV2

Formulas

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

ashishbarot · Aug 27, 2009

Sir i tried like this;.......but how to use this function with Min i.e.

I will have the data like this

VVS1 VVS2 SI1 .......I want see which one is the highest from them result should b VVS1

Public Function IFL() As Long
IFL = 1
End Function

Public Function VVS1() As Long
VVS1 = 2
End Function

Public Function VVS2() As Long
VVS2 = 3
End Function

Public Function VS1() As Long
VS1 = 4
End Function

Public Function VS2() As Long
VS2 = 5
End Function

Public Function SI1() As Long
SI1 = 6
End Function

Public Function SI2() As Long
SI2 = 7
End Function

Public Function SI3() As Long
SI3 = 8
End Function

yytsunamiyy · Aug 27, 2009

see edited post #17

Hermanito · Aug 27, 2009

When you have the functions ready, use them like this:
'=MIN(VVS1(),VVS2(),IFL(),...)'

User Defined Functions

ashishbarot

New Member

ashishbarot

New Member

Excel Facts

Hermanito

Well-known Member

ashishbarot

New Member

ashishbarot

New Member

Hermanito

Well-known Member

Hermanito

Well-known Member

yytsunamiyy

Well-known Member

ashishbarot

New Member

yytsunamiyy

Well-known Member

Hermanito

Well-known Member

Similar threads

Forum statistics

Share this page

User Defined Functions

New Member

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock