Formula To Calculate Result Based On Range

Dec 13, 2012

I have a scorecard that looks something like this:

90% or greater=3

I need the "cell" to react accordingly and I'm lost.

View 2 Replies


Automatically Format Range Based On Formula Result Of Cells

May 16, 2009

The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:

1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................

View 4 Replies View Related

Calculate End Result Based On Cell Contents Of 3 Columns

Jan 13, 2014

I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F

For example:

Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7

I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]

Here are an example of what I have tried, amongst many...

=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.

View 4 Replies View Related

Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met

Apr 28, 2008

Firstly its probably best you download the file from below and open it up.

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be 80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is 80.39.

If for example the exchange charge displayed 0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

View 12 Replies View Related

Formula To Calculate Result Whilst Avoiding Errors Is Too Long!

Jan 22, 2010

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

View 3 Replies View Related

Return Result Based On Percentage Being Within A Range

Jan 17, 2008

I'm attempting to do the following:

A1 contains names of projects.

B1 contains variances from budgets, being positive or negative numbers.

C1 will contain results.

I need to know which of the B1 cells contain a number equal to or greater than 20%/-20% and output the names in A1 that match the criteria to C3.

View 8 Replies View Related

Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table

Oct 19, 2006

I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.

| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
| Jan | $100 | $250 | $300 |
| Feb | $200 | $300 | $200 |
| Mar | $300 | $250 | $100 |
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
| Jan | $100 | $250 | $300 |
| Feb | $200 | $300 | $200 |
| Mar | $300 | $250 | $100 |
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #

The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?

View 8 Replies View Related

Return 1,2 Or 3 Based On Result Of Formula

Mar 3, 2007

Any other day but today (I've been starring at numbers too long) I could do this.

I need a simple formula that will return:

a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if

View 9 Replies View Related

Array Formula Result In To Range

Jun 7, 2009

I have array formula {=row(indirect("1:5")} which is result to {1;2;3;4;5} and it look like a range.

And i want to insert this into formula COUNTIF at the range section
i'd try it, but it didnt worked.

(its look like insert array formula into range section of a formula)

View 9 Replies View Related

Returning A Certain Result Based On A Specific Data Range

Nov 26, 2009

I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.

I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.

View 3 Replies View Related

Create Autoshape Based On Formula Result

Aug 1, 2006

how to have a cell equate to an autoshape based on the results of an "if" statement?

View 4 Replies View Related

Color Formula Result Based On Criteria

Jan 4, 2007

I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.

View 2 Replies View Related

Hide Formula Result When No Data In Range

Aug 20, 2008

I have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:


Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.

View 5 Replies View Related

Find/Locate End Of Range & Add Formula Result

Aug 24, 2006

Dim myDynRange As Range
Dim myLastRow As Integer
myLastRow = Range("H:H"). CurrentRegion.Rows.Count
myFirstCell = Cells(Counter, 8).Address
myLastCell = Cells(myLastRow, 8).Address
myDynRange = myFirstCell & myLastCell

im having a problem with the last line
myDynRange = myFirstCell & myLastCell

my eventual aim is to do an average on this column with a line like

ActiveCell.Offset(Counter, 1).Value = Application.WorksheetFunction.average(myDynRange)

the reason why my range declaration is so strage is because i was trying to change the dimensions of the range with an offset function because i had many ranges to average but the offset function didnt want to work. this way goes like this....................

View 2 Replies View Related

Formula For Students - Result Based On Multiple Criteria

Aug 5, 2014

How to formulate results of students in excel sheet.

From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:

1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year

The rules are in the attached picture flowchart.jpg

I am unsuccessful in writing a formula for such multiple criteria...

View 12 Replies View Related

Conditional Format Based On Formula Result Being A Whole Number

Dec 6, 2012

I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.

I don't want to include the formula in the sheet itself, just have that as the formula in the condition.

The formula will be along the lines of:

=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)

My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.

View 4 Replies View Related

How To Move Data From One Worksheet To Another Based On Formula Result

Jun 20, 2013

I have a formula sheet that uses an IF statement to determine if one columns data is bigger than another. Out of the 300 or so rows there are approx 20 that come back as yes (this is in column A) and the rest are blank

What I would like to do, is for the 20 or so rows, I would like to pull out (copy) columns B, J and L and put them into worksheet 2. Preferably without any gaps in the rows or columns.

I hope this is enough information, I am using MS excel version 2010 although I think the people who will ultimately be using it are on an earlier version.

View 6 Replies View Related

Formula That Returns A Result Based On All Dates In Column A

Feb 15, 2007

I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.

Column A
1/2/2007 0:00
1/3/2007 0:00
1/10/2007 0:00
2/10/2007 0:00
2/10/2007 0:00
2/15/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
4/31/2007 0:00

Column B
Invoice #

I have this formula in cell F3 that will add the dates through the end of January:

View 10 Replies View Related

Run Macro Based On Result Of IF Function/Formula In Cell

Sep 14, 2006

how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.

View 2 Replies View Related

Formula: Return Result Based On Other Cell Values

Sep 27, 2006

I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:

if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"

So on and so on.... I would really appreciate your help on this issue.

I have used excel for some time now but not with complex formulas or any vb.

View 9 Replies View Related

Auto Hide Column Based On Formula Result

Aug 5, 2008

I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub

Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.

View 4 Replies View Related

Sum Result Of Formula On A Range Of Cells That Meet Criteria

May 20, 2013

Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).

In this case the result I'm looking for is on row 2:

Is this possible to calculate in 1 cell only (h1)?






View 3 Replies View Related

Change Range Format & Color Based On Result Of Drop-Down Choice

Jul 6, 2009

I'm using Excel 2007.

I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line.

OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part.

If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again.

I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.

View 9 Replies View Related

Convert Result Of Formula To $ From HNL Based On Chose In Validation Column

Feb 20, 2014

We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.

View 2 Replies View Related

Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result

May 24, 2008

I'm quite a novice at Excel. I have a column of values that I sum as follows;


0 <----------------sum of A1:A5

A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.

View 7 Replies View Related

Dynamic Lookup Formula - Retrieving Result From A Set Of Range From Other Sheet

Sep 27, 2011

I have a query on retrieving the result from a set of range from other sheet.

Query: In the Sheet1 Column B has to take first two words from the
Column A & lookup the matching results from Sheet 2.
(The search range should be the first two words of each cell)

Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B

Example: The Expected result should be as mentioned below. (Blue in color should be the results)

Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5
Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A

Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14

This is my query

View 7 Replies View Related

Excel 2003 :: Cell Result Based On Range Of Filled / Empty Cells

Apr 3, 2014

I'm stuck using Excel 2003 to auto-populate a cell.

I have a range of dates in five consecutive columns called:

Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5

I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.

Each phase is consecutive to the next so will always be filled in from 1 to 5.

I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.

I've tried nesting some ISBLANK functions without any luck.

View 4 Replies View Related

Calculate The Range And Fill With A Formula Using The New Range

Apr 1, 2009

I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.


Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)

Excel tables to the web >>" target="_blank"> Excel Jeanie HTML 4

View 9 Replies View Related

VLOOKUP Formula Dragdown Copies Previous Cell Result Instead Of Unique Result

Jun 10, 2014

When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.

When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.

I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.

View 3 Replies View Related

Formula To Calculate A Fee Based On Performance

Sep 8, 2009

I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:


I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEYMin FeeFee 1Fee 2Fee 3Fee 4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999

So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

View 13 Replies View Related

Copyrights 2005-15, All rights reserved