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;
A
0
0
0
0
0
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.
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.
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.
In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
I am working on a model for a school project. Time is in ColA, the number of events is in ColB.
Each event requires one person to work before and after the event. For example, if we assume that I need one person 30 min before the event, one during the event and one 15 min after the event - ColC shows the number of staff required. I would need a formula to calculate ColC. Basically I want excel to add the values of the rows above and below a value if it is within a specified time range
It gets a little bit more complicated unfortunately, the time requirements need to be dynamic - so one day it is 30 min before the event, but another day it could only be 15 min before but 30 min after the event etc.
I was trying to do this with a SUMIF formulas, but couldn't get it to work.
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.
i have excel sheet with name labels.i want count how many times user repated along with his branch code. acctully am done this using this formula, but not getting what am exepected.
Formula in B1 is ="JSK-SW-1"&(countif($A$2:$A2,$A2)) output is value is incremented, but i want text also be change. A B Name Count Sateesh JSK-SW-1 Rajesh Raj- SE-1 Sateesh JSK-SW-2 Rajesh Raj-SE-2 Anil JAK-DE-1
In my excel workbook, I have a customer table and invoice sheet among many others.
As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.
Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.
For example, customer called Bob (Bob-1); Number of purchases = 1 Customer Type = Single Discount = Yes
Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:
Number of purchases = 2 Customer Type = Multiple (can be achieved by using IF statement on No. of purchases) Discount = No
What I'm trying to do is from a template worksheet that I have in a workbook with other worksheets, I'm trying to come up with code that will create a new worksheet based on the template (copy), increment the invoice number, and rename the worksheet tab to be "Invoice # xxxx" (new invoice number from prev. step.).
I have figured out how to use a button on the template to execute, but as I said my VBA skills are lacking to say the least.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
Line (A1) asdfasdf Line (A2) adsfadsf Line (A3) asdfasdf
What I would like to be able to do is highlight the three columns and fill down, but I would like for it to increment and paste the next three lines filled like this:
Line (A2) asdfasdf Line (A3) adsfadsf Line (A4) asdfasdf
Then below that the next three lines filled like this:
Line (A3) asdfasdf Line (A4) adsfadsf Line (A5) asdfasdf
The (A1, A2, etc.) are cell ranges and already contain the concatenate function that pulls data from a different location. The problem I am having is when I
highlight and fill down, it doesn't increment the cell by +1 (Ex. A1 beomes A2) but instead by +3 (A1 becomes A4) the number of lines highlighted.
I have set of column data A & B In those A contains Serial No & B contains Ticket No...
I am looking for formula to solve the function by those conditions... almost get done by countifs functions.
1.If Serial & Ticket No Only Once result should be "FIRST TIME LABOUR ONLY" 2.If Repeat Twice For The First Serial No And Ticket "Labour Only", For The Second Serial No And Ticket "Labour & Parts" 3.If Serial No Twice But Ticekt No is different for both serial no Result Should be "PART USED 1 OF 2" 4.More Than 2 Times Serial & Ticket No Repeats" Result Should Be "CCI"
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.
I am having a difficult time with a calculation. I took over someone else's duty who is no longer with the company. The calculation used did not account for entire columns & the data I need to pull fromhas grown larger than the calculation. When I hit "F2" to manually change the data array, everything works fine until I hit enter and then the result displays as 0.0%. When I hit the insert function button, the result shows as the correct number. I tried copying the format only from an adjacent cell, but this did not work. When I tried to undo my changes, the cell still displays as 0.0%
The original calc is: =SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998=9,1,IF(cData!$N$2:$N$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998<>"",1,0),""))
The new calc is: =SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536=9,1,IF(cData!$N$2:$N$65536=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536<>"",1,0),""))
The calc from the cell below the cell I am working in: =SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998=9,1,IF(cData!$O$2:$O$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998<>"",1,0),""))
I am doing some data analysis and need to figure out whether a number in column B is greater than a number in column A by £X.XX (Number to be determined) - is there a function, or custom function that would let me do this?
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
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...
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.
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.
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 Else 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.
i have a sheet which adds up stock = SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero.
The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)
Like this;
=('Input &B1 2014'!$BI$3)/1000
Or must I manually go in and replace all formulas for each month?
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.
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..................
