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
Feb 1, 2013
How can we convert time on a scale of 100 to a scale of 60?
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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