Using IF Function To Create Scale

Jul 23, 2014

I am trying to create a scale using IF functions in Excel.

I have a row of numbers that I want to convert to my scale. My scale is fairly simple, less than 1 =1 and more than 10 = 1
After that, any number between 1-2 gets a 10, any number between 2-3 gets a 9, any number between 3-4 gets a 8, etc.

So, what I have done is take a logic if-then function and applied it to my row of numbers. It looks something like this:

=IF(2<D2<3,9)

However, this doesn't work. I have a number (2.55), and instead of showing a 9, it says FALSE. I am pretty sure 2.55 falls between 2 and 3 on the scale. Why won't it work?

Also, is there any way to create a function where I could put each of my if/then functions to create my whole scale? Right now I just have 10 separate columns for each number on my scale.

View 1 Replies


ADVERTISEMENT

Convert Time On Scale Of 100 To Scale Of 60?

Feb 1, 2013

How can we convert time on a scale of 100 to a scale of 60?

View 3 Replies View Related

IF Function Which Will Create Y For 1 Or 2

Jan 5, 2013

In column A, I have numbers ranging from 0-6. In column B, I would like to create a "Y" each time there is a 1 or 2 in column A. How can I create an IF function which will create a Y for a 1 or 2?

View 4 Replies View Related

How To Correctly Create OR Function

Mar 3, 2014

I should i make this formula

A customer is eligible for a discount if the customer’s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"

So far i have in the IF function
=IF([@[2009 sales]]>= 200000 ,"Y","N")

How should i insert if the customers OR First Order was placed in 2012?

View 2 Replies View Related

Create New Worksheet With Function

Jun 20, 2008

Can you have a function create a new worksheet? i know this is possible with a Sub but i haven't been able to do this with a function. Example is below. I'm planning to call the function from an Excel cell (i.e. =CreateNewSheet())

Function CreateNewSheet()
Sheets.Add
End Function

View 8 Replies View Related

Function To Create Currency Pairs

Jun 22, 2014

I am trying to figure out a function that creates currency pairs, for example EUR/USD etc based on a limitation.

I attach you a workbook with the details. The first step I have accomplished, I am having trouble to the second step as you will see in the sheet.

Test.xlsx

View 8 Replies View Related

Excel 2010 :: Function To Create PDF

Jan 24, 2014

The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.

So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.

Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?

View 4 Replies View Related

Create User Defined Function With IF And AND

Jul 14, 2014

I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function

My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")

LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300

Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"

End If

End Function

View 3 Replies View Related

Create Formula Using COUNTIF Function?

Dec 4, 2012

create a formula using the COUNTIF function? I have a number in cell C1 and then numbers from C3:C65. I would like to produce a number count of how many items from c3:c65 are less than the value in c1 which is 24.

View 6 Replies View Related

Create IF Function That Searches For Word No?

Jul 28, 2014

I am trying to create an IF function that searches for the word "No." I have created a table with monthly payments and if the payment is >= 0, it returns the word Yes. If the payment is

View 3 Replies View Related

Using INDIRECT Function To Create Custom Validation

Jun 28, 2013

I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx

Name = Color
Values = Blue, Red, Green

Name = Shape
Values = Square, Circle, Triangle

So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.

When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.

In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).

VB:
Dim i As Long
Dim lastRow As Long
Dim sht As worksheet

[Code] .....

As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:

"=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN()), 0,-1)))"

Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.

For reference, my code is this:

VB:
Private Sub cmdRefreshValidation_Click()
'Re-enters validation parameters to all cells to allow selections
Dim i As Long
Dim lastRow As Long
Dim sht As Worksheet

[Code] .....

View 1 Replies View Related

Create Function That Reads Cell Of Different Sheet

Feb 4, 2014

I want to create a formula that reads cell B6 of a different sheet. The sheet name is in cell A1, and I want to get the results in B1.

Later I will change A1, and I want to get in B1 the cell B6 from the different sheet. What is the formula in B1.

View 1 Replies View Related

Create A Function That Adds A Predetermined Amount?

Aug 4, 2014

I need to create a function that adds a predetermined amount (lets say 120) to a cell (lets say A1) every time another cell (lets say B1) reaches a 10 (like 10,20,30,40 etc)

Lets say the B1 is 30, i need A1 to recognize that it needs to show 360 as its value (adding 120 for every 10 of B1)

View 2 Replies View Related

How To Create Custom Copy And Paste Function

Jan 15, 2013

How would I create a shortcut to automatically copy the contents of cell H1 to cell K1, in other word I would like to hit a shortcut key and have the contents of that cell pasted 3 cells to the right on the same line. I have to do this over and over again down the worksheet. I am working on taxes and want to copy values over to the expenses column as I find them.

I would also like to know if it is possible while having cell D1 highlighted I could have a shortcut created that would copy the value three cells over in H1 to cell K1. That would be the fastest, but I don't know if it is possible?

View 2 Replies View Related

Run Macro Function To Create A Relative Reference

Oct 1, 2008

I'm a definite newbie when it comes to macros but can figure out formulas ok.

So I wrote a formula to break out a sort string and it got pretty complicated so I’m trying to create a macro so I don't have to write out the formula all the time. However the sort tag can be in different columns when I need this formula so I tried the "run macro" function to create a relative reference.

So it changed my formula from this:

PHP
=IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)),"",IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2)+1)&")",EM2)),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,100),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,FIND("("&(COLUMN()-COLUMN(EM2))+1&")",EM2)-FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)-3))) 

PHP
 "=IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])),"""",IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1])+1)&"")"",RC[-1])),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,100),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,FIND(""(""&(COLUMN()-COLUMN(RC[-1]))+1&"")"",RC[-1])-FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])-3)))" 

Now my problem is in the first formula I need "$EM2" not "EM2".

I tried the obvious to me fix of R$C[-1] which did not work... And when trying to re-run the macro it puts RC143 which will not work because the sort string is not always in column EM.

View 8 Replies View Related

Create User Input For LARGE Function

May 18, 2009

I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.

=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))

I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.

View 2 Replies View Related

Create A Macro From This Simple Search Function

Aug 24, 2009

I am using excel 2007 for database of a large e-commerce site.
I am creating search-able attributes from keywords found in description, title and meta-data columns.

I am using the following function in "column X" to search 4 columns (see formula) for specific music styles.

View 6 Replies View Related

Create List Of Results With Lookup Function?

Mar 16, 2012

I'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.

Here is some workbook info:

Invoice generator is in 'phonelookup'
Invoice database is in 'invoice database' and the table is called CompInvoices
In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.

View 1 Replies View Related

Create String Function With Range Of Numbers

Sep 11, 2012

Is it possible to create a string with a range of numbers

1
3
4
5
6
8
10

Result: 1,3:6,8,10

Or

1
2
3
4

Result: 1:4

Or

1
3
5
7

Result: 1,3,5,7

Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.

Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells

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

View 9 Replies View Related

How To Create Formula Helper For Custom Function

Mar 26, 2014

I've created a custom Function. I would like for there to be a Helper when a user is entering the Function arguments.

For example if I go into a cell and type in =VLOOKUP(

A popup with appear that shows...VLOOKUP(Lookup_Value,Table_Array,col_index_num,[range_lookup]).

Is it possible for me to build something like this in my Function?

View 4 Replies View Related

Trying To Create A Conditional Product Function In XL2003

Sep 4, 2008

I'm trying to do:

Column A is full of daily consecutive dates, Columns B to H are full of numbers.

I'd like to create function that accepts 2 dates and a column reference as input like this:

function(date1, date2, columnnumber)

And as output it gives the product of all the numbers in that referenced column between the two dates.

View 9 Replies View Related

To Create A Function Macro Called FSTD

Nov 5, 2008

i need to create a function macro Called FSTD that inserts words into a cell and conditionally formats them depending on th number in the cell on the left.

in the sheet there are ranges that the number will fall between.

Example

Cell B1 is the cell were i want to run the function in
Cell A1 is the cell with the number that is used in the function

Cells A3:A7 are that ranges that i need to use in the function

E.g. if the number in A1 is less than A6 = i want "P" to appear in the cell with a red backround

if the number is greater than A6 but less than A5 = i want "M" to apear with an orange back round

if the number is Greater than A5 but less than A4 = i want "E" to apear with a green backround

even if it is possible to do without changing the backrounds, just to insert the letters

View 9 Replies View Related

Create A Separate Function To Sum All The A1's? Below Is An Extract Of My Sub Routine.

Jul 18, 2009

For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
x = wksht.Range("A1:B2").Value

Next wksht

View 9 Replies View Related

Create Function To Balance Items That Are Subordinate

Jan 29, 2007

I am trying to enhance our estimating spreadsheet. If you look at the example attached it is a snippet of the spreadsheet. What I am trying to do is make column AJ (balanced bid unit) include subordinate items. The way a sub item will be defined is column B (bid item) if column B = S, then its balanced bid unit need to be reflected in the column AJ. I would also any item titled as Y in column B (bid item) to include all listings as S (until next item as Y) in the column AJ totals.

View 9 Replies View Related

Scale In X Axis

Apr 12, 2007

The scale on our chart in the X axis is mandating a numeric number. It is showing "39200" instead of 4/28/07. In any event, we would like to have 4/9/07 -- the last date we have data for and the last date that we have referenced on the X axis.

The chart is in the attached chart.xls and the data is in the attached chart.xls

View 9 Replies View Related

Create Function To Choose And Display Optimal Configuration Of 5 Box CBM

Feb 27, 2014

I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.

How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.

Snippet attached:

Capture.PNG

View 1 Replies View Related

Create A VBA Function That Can Calculate The CRC32 Checksum For A File?

Oct 10, 2013

I want to create a VBA function that can calculate the CRC32 checksum for a file.

I found this webpage which looks promising: http:[url].....

The only catch is that the code provided is VB not VBA. How hard would it be to tweak this code to run in VBA?

View 10 Replies View Related

VBA Code To Create SUMIF Function That Adds Comments?

Feb 28, 2013

how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.

View 1 Replies View Related

Create Function That Consolidates Part Numbers In Two Columns?

Mar 25, 2014

I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.

PART
QTY

CONSOLIDATED PARTS
CONSOLIDATED
QUANTITIES

View 3 Replies View Related

Sliding Percentage Scale

Jun 18, 2014

A set low number of 100 is in cell B4, and a related set high percent of 18% is in cell B6.

A set high number of 1,000 is in cell D4, and a related set low percent of 14% is in cell D6.

In cell B8 a manual number will be typed between 100 and 1,000.

In cell B10 we want a formula that will roundup a percentage number between 14% and 18% depending on the number inserted in B8.

View 7 Replies View Related







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