Formula For Entering The Lower Of 2 Options

Sep 21, 2006

I will enter a figure into A1. I will also enter a figure into C1. In E1 I would like to enter the lower of: 0.2% of A1 or 75% of C1

View 5 Replies


ADVERTISEMENT

Copying Formula To 8 Cells Lower With Result Of 1 Cell Lower?

Sep 30, 2013

create a formula copying a formula from cell a5 to a20 but i want the result to be 1 cell lower from a5's result

View 6 Replies View Related

If Formula With A Upper And Lower Value Limits

Apr 19, 2009

The values in F4:F14 are changed depending on the value in F2. The rows in green have a fixed value. The row in yellow has a lower limit (170)and upper limit (195). I cannot get a formula in F14 to perform this function.

View 3 Replies View Related

Formula: Higher, Lower Or The Same To A Specific Cell Value

Aug 24, 2009

I have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.

View 12 Replies View Related

Formula To Calculate UPPER And LOWER Control Limits

Nov 12, 2010

I have a range of numbers:

A1=24
A2=17
A3=9
A4=4

Based on this the MEAN=13.5 and STANDARD DEVIATION= 8.81286937760152

I want to create a formula to calculate the UCL and LCL

When I use MINITAB I get UCL=31.23 & LCL=-4.23

View 7 Replies View Related

If Formula With 3 Options

Nov 25, 2006

I am currently having trouble getting =IF forumla's to work, with more than 3 options AND an equation being taken into account. Attached, is an example of a spreadsheet, involving what I currently have, with Row 5 showing the formula's I've used, and rows 2-4 showing examples of 'Yes', 'No' and 'Equal' outcomes.

For Column E, I would like the value from the above cell (set to zero in the formula of the first cell) to:
-Go up 1 digit (from the above cell's value) for a Yes in that rows C cell
-Go down 1 digit (from the above cells value) for a No in that rows C cell
-Go up 2 digit (from the above cell's value) for a Equal in that rows C cell

As can also be seen from my example, I only have it working so that 'Yes' in C will take the value up by 1, and anything else (including 'No' and 'Equal') going down a point, which is only really using 2 options. I have tried numerous different possibilities for trying to get this to work, all of which gave me #VALUE error, or wouldn't even let me accept the formula. My current formula for what I have now (with only 2 options) is

=If(C5="Yes",E4+1, E1-1)

Basically, if possible, I would just like to make the E column change from 2 different ways (e.g +1 and -1) to 3+ different ways (e.g +1, +2 and -1).

View 3 Replies View Related

Formula In Lower Table To Fill In The Missing County Cells

Sep 1, 2009

I am looking for a formula that I can use in the lower table to fill in the missing County cells, based on the values in the Town/Zip columns on the top table (I apoligize for the crudeness):

CountyTownZipAbleTownA00000AbleTownB00001AbleTownC00002BravoTownD00003BravoTownE00004CountyTownZipMemberTownD00003BlackTownC00002FrankTownA00000GreeneTownB00001JonesTownA00000SmithTownC00006ThomasTownE00004White

This would consist of hundreds of Zips and Towns and this is just an abbreviated mock up.

View 9 Replies View Related

Excel Formula For More Than 10 Options?

Jan 22, 2013

i have 2 columns i want a formula that will test both cells at the same time for different possibilities:

First Column Second Column
IN Place 1
Not In Place -1
Part In Place 0

I need to check for all these possibilities and return a grade for it

View 9 Replies View Related

Formula For Different Options To Return Value Based Upon Selections?

Jun 25, 2014

formula for when i select from the drop-down boxes it returns a value that i have placed next to it on a separate sheet. I have attached an example of what i am trying to accomplish

View 3 Replies View Related

Error When Entering Formula Using VBA?

May 29, 2014

I have a formula that I'm using which works and gives me the right value.

Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)

However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.

Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"

View 3 Replies View Related

Entering A Formula As An Array In VBA

Oct 20, 2008

My VBA sub contains
DIM sFormula As String
DIM rCell as Range

at some point in the code, the value of sFormula is set to something like
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)

When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.

View 9 Replies View Related

Formula To Have One Cell Display Multiple Options Depending On Value In Another Box

Oct 1, 2013

I need to build a formula to have one cell display multiple options depending on a value in another box.

If Value is >x and <x display Y

Details:

Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:

If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.

View 2 Replies View Related

Formula Not Updating: Using Tools, Options, Calculation, Automatic

Oct 27, 2006

I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in Tools-Options, that doesn't work either.

View 3 Replies View Related

Formula That Will Get Word To Appear After Entering All Information?

Aug 8, 2014

I have been looking for a forumla that will put an 'and' between the last two bits of information.

Joe Bloggs
Peter Smith
Matt Jones

I have list of names in individual cells like above. I have figured out a formula that will put them into a single line (Joe Bloggs, Peter Smith, Matt Jones). But what I need is a formula that will put it into single line and insert an 'and' ALWAYS between the last two people. So it should show like (Joe Bloggs, Peter Smith and Matt Jones) but if I remove Matt Jones it should now display as (Joe Bloggs and Peter Smith).

View 11 Replies View Related

Entering A Formula To Determine A Grade

Apr 6, 2007

I would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:

Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%

each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.

I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.

Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?

View 9 Replies View Related

Prevent Someone From Entering A Formula In A Cell

May 18, 2007

I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?

View 5 Replies View Related

Determine Values In Cells: Get The Lower Values Between To Cells And Have The Lower Valued Cell Highlighted

Oct 27, 2009

I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?

View 2 Replies View Related

Formula For Highlighting Whole Row After Entering A Date In One Of The Columns?

May 21, 2014

how to do the formula for highlighting whole row after entering a date in one of the columns.this spread sheet had over 300 rows so I can't do the conditional formatting, I need a formula for whole spreadsheet.

View 11 Replies View Related

Looping Through All Rows And Entering Formula To Calculate Change?

Oct 2, 2012

I want to create a loop that goes down all the rows in my spreadsheet and does the formula (end/beginning -1) for all the rows of cells. The "end" cell is the cell that is the farthest right in the row (some sort of end.xlright) and the "beginning" cell is column D of the row that is being calculated.

View 4 Replies View Related

Formula Is Entering A Default Time When It Comes Across An Empty Cell

Nov 9, 2005

I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.

However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:

=sheet1!A1

e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

View 10 Replies View Related

Entering Formula- Data Imported From Access And Saved As A Worksheet

Dec 7, 2007

I have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).

View 12 Replies View Related

Formula Entering Zip Code And Returning Tax Rate From Established List

Oct 29, 2013

I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.

View 1 Replies View Related

Only Allow Upper/Lower Case Not All Upper Or All Lower

Oct 8, 2008

Is there a data validation that will not allow all upper case or all lower case characters?

For examples:

Allow:
John Doe
101A N Woodbine Street

Disallow:
JOHN DOE
101A N WOODBINE STREET

Disallow:
john doe
101a n woodbine street

Or perhaps formatting that would convert the words to upper/lower case?

Or, I would be interested in VBA if it is the only way.

View 9 Replies View Related

How To Increase Value To Larger And Not Lower

Nov 26, 2012

I have 83,33 value. If I like to increase into 84 and not 83 without decimal point

View 11 Replies View Related

Return Set Value If Cell Is Lower

May 11, 2012

I am trying to get a simple formula to look in A1 and if it is lower than A2 then return the value in A1 if it is not then return the value in A2..

View 4 Replies View Related

Upper And Lower Case In VBA

Sep 22, 2008

I have this code (this is just a snippet)

For Each cel In Range("E6:AI15").Cells
If IsError(cel.Value) Then
Else
Select Case UCase(cel.Value)
Case "H"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 3
Case "S"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 10

but I want the cells to accept both upper and lower case values and format the cells accordingly ie. H or h, S or s

View 9 Replies View Related

Lower Case To CAPS

Oct 6, 2009

I have looked in conditional formatting, but do not see a way to change lower case to all caps. Is there a way to do this so that when someone types in a cell it returns all caps instead of leaving it in lower case?

View 9 Replies View Related

Change Character UPPER TO LOWER

Jul 21, 2014

I need to change the all Upper Case character to Lower case character for all cells. See below my code, i am getting error message, Tye Mismatch. "cell.Value = LCase(cell.Value)"

[Code] .....

View 3 Replies View Related

Promt Warning Msg If Certain Cell Value Is Lower Then Another

Mar 5, 2008

i wanted to know if there is a way which i can so that would tell excel if a certain cell value is lower then another it would give a msg
for example

Cell A1 - 5
so if cell A2 is less then five and a macro is clicked then the macro would give a warning msg hence ending the macro

View 11 Replies View Related

How To Change Lower Case To Uppercase

Apr 28, 2009

How to change the lowercase letters to uppercase letters?

While using the userform, for example "name" is entered in the form.

My requirement is the values should reflect as uppercase letters like "NAME"

View 2 Replies View Related







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