Multiple Calculations From Different Cells With Result In One Cell?
Mar 19, 2014
I realised that the screenshot attachment makes much more sense than trying to show within post.
EXPENSE MONTHLY ANNUALLY MONTHLY COST ANNUAL COST
BILL £4.00 £4.00 £48.00
BILL £120.00 £10.00 £120.00
BILL £260.00 £21.67 £260.00
BILL £12.00 £12.00 £144.00
BILL £19.00 £19.00 £228.00
BILL £14.63 £14.63 £175.56
BILL £550.00 £45.83 £550.00
BILL £94.00 £94.00 £1,128.00
For my bills I want to work out first the monthly cost e.g. monthly(B) figure OR annual (C) /12 - and give the result in column (D)
And then work out the annual cost e.g. monthly (B) *12 OR annual figure - and give the result in column (E)
So if there is no figure in the monthly column (B), the calculation will be dividing the annual column figure by 12 and vice versa, if there is no figure in the annual column (C) then the calculation will be multiplying the figure by 12
This is probably a very simple calculation to do, but I am struggling to make sense of calculating results from different columns to give a result in the same cell.
I have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range.
I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$12" Then If Target.Value < 0.65 Then Run "MyMacro" End If End If End Sub
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I have a worksheet and in two of the cells I have text. In another cell I want to put a formula in so that if certain text is typed into each of the two cells a result will appear in the third cell.
Hazardous Smiths 60%
Non Hazardous Smiths 20%
Hazardous Browns 45%
Can I put a formula in and if so, what formula would it be?
I would like to gray out or hide contents of a series or group of cells when a particular cell has a certain result. i.e. if cell A1 is <5 I want cell rows 5 thru 10 to be hidden or grayed out.
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 a spreadsheet that I am trying to have automatically calculate a total based on certain criteria:
I want the amount under the per diem amount column W to return a value based on if P is entered in column V. If this is for partial it will depend on departure and arrival times. If departure is after 6:00 AM no breakfast per diem is paid, if departure is after 11:00 AM no lunch is paid. If arrival is before 2:00 PM no lunch. If arrival is before 7:00 PM no dinner. If the user enters a 1 or 2 in column U, 1 uses out of state per diem breakdown located in cells I38:I40. In state uses a 2 and is located in cells E38:E40.
I want to pay different commission rates for different levels of sales...
IE nothing if sales are under 250,000 per year. 5% between 250,000 and 500,000, 8 % between 500,000 and 750,000 10% between 750,000 and 1,000,000 12.5 % between 1,000,000 and 1,250,000 and 15 % over 1,250,000
The issue that im having trouble with is that if the sales guy brings in 1,500,000 in yeary sales he would be paid some at 0%, some at 5% some at 8% some at 10% , so at 12.5 and some at 15%
How do i calculate that? I have included a excel spreadsheet.
I am creating a userform that has multiple calculations in it. I understand how to do this in Excel but I have no idea what the order of operations would be with a user form. (UoM Cost) will = Unit Price / Quantity). The (Ext Cost) field would equal [Quantity x Waste% x UoM Cost (that needs to be calculated before) + Unit Price. (Cost Per Each) would = the "Ext Cost"....that needs to be calculated before / Quantity - Waste%.
Once I have these calculations, then need to click a button to either reset the user for for another record (or if I mess up) and a button to save the record to the excel spreadsheet in the background.
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them.
for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
I have a start time and a stop time, then a difference is calculated in cell L53. I want to subtract the time value of a break but it only works if the entered value is greater than 1:00. I tried K2-I2-L55 and It works but It wont work for times less than 1:00. I need to be able to subtract :15, :30 or any other value in cell L53.
I am trying to track the time the various activities take. I need this in hours:minutes - Monday through Friday. Attached is the spreadsheet that does NOT work. An "x" in the column creates the timestamp in the adjacent column. Some of my formulas are not correct as the calculations do not create the correct data.
I'm working on this excel tool that I would like to make as user-friendly as possible. It would act like a survey almost where the user would choose responses or input values for specific questions and then on other worksheets it would have the actual calculation and math.
I see that if one were to add a value onto worksheet 1 and which would be a variable that is essential to a calculation on worksheet 2 it does not run automatically.
If there is a combo box w text values that also needs to have a calculation on another page, can i just do an IF statement on worksheet 2 with combo boxes?
I have one spreadsheet that contains employee overtime info per pay period which is bi-weekly, another spread that has their monthly production numbers. So...
Employee OT PayEndDate John Smith 5 01/13/12 John Smith 7 01/27/12 Jack Jones 8 01/13/12 Jack Jones 9 01/27/12 John Smith 6 02/10/12 John Smith 3 02/24/12 Jack Jones 8 02/10/12 Jack Jones 10 02/24/12 And so on
On the other sheet Employee Production Month Jonh Smith 53 Jan Jack Jones 75 Jan John Smith 45 Feb Jack Jones 80 Feb And so on,
What I need to do is see the overtime hours per month, and the production numbers for the same month. Something like...
Employee OT Production Month Jonh Smith 12 53 Jan Jack Jones 17 75 Jan John Smith 9 45 Feb Jack Jones 18 80 Feb
I have a pivot table the sums up the OT by PayEnd Date but need to change PayEndDate to month, which I know how to do, but then add in the production any thoughts on how I could do this?
How to streamline calculations that include multiple variables.
I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:
Associate Role Function 1 Count Function 1 Time Function 1 Learning Curve Function 2 Count Function 2 Time Function 2 Learning Curve Function 3 Count Function 3 Time Function 3 Learning Curve
[Code] .....
There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:
I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.
I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.
Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.
In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.
Is there any way to combine all the following calculations? Using the first as an example - If the word Resigned does not appear in P28 and if S28 = Q then the sum is 6 - 6*O28
having a different calculations performed within a Textbox on a Userform depending on the selection made in a Combo Box. This was answered here.
Perform Calculation In Textbox On Userform
I have another question regarding this problem, however the thread is closed so I have posted a new thread.
The original question was answered and the solution works very well,however I now need to be able to use the selection from two (2) comboboxes to initiate the various calculations in a similar vain.
have a look at the original thread to get an idea of what I am looking for here.
I have tried many configurations of various code, all of which works to an extent buts which falls over in certain circumstances.
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.
I have a spreadsheet which has number of formatted rows, the user can insert the formatted rows if they need more as its hard to determine if they will need one set or 50 sets.
The formatted cells are 4 rows deep and span from columns A to AR
The thing is that I want the totals at the bottom to automatically pick up the sums from say Column H but only on the 2nd row of each set of formatted cells eg. H13, H17, H21, H25 etc.
And another sum to pick up the 3rd row, e.g H14, H18, H22, H26 etc
Obviously this can be done manually by selecting control and AutoSum but I wondered if there was a pice of VBA code or a formula that i could run due to some worksheets being longer than others?
I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
I have a row of raw quality control data, but some are not in control and should not be used in the calculation of statistical data. There are not a lot of them, so I can manually select these (I use a red fill color for the cell to signify bad data), but I would like to be able to keep these data displayed but not have them included in the calculations.
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project Benefit Type Delivered or Enabled Benefit
PJ1 Financial Delivered Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered Financial - Enabled Tech - Delivered Tech - Enabled Green - Delivered Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
