Multiplying And Subtracting Within Cells
Feb 5, 2014
I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.
View 1 Replies
ADVERTISEMENT
Dec 22, 2008
This is about my misformatted download again. It considers all the numbers to be text. Naturally, I need them to be numbers. I discovered a page on the Web that says if you multiply all the numbers by 1, suddenly Excel gets a clue and figures out they are numbers. I've tried this (as the page recommended) by using Paste Special, and it works. I know the command I need to use to get it to Paste Special:
View 3 Replies
View Related
Oct 12, 2012
I want to merge some cells using a criteria. To be more specific, let's speak on the file attached.
Beginning with the P5 cell I want to merge to the right groups of 4 cells for each name from the column J. Therefore the first group of merged cells will be P5:S5, the next group will be T5:W5 and so on until reaches 11 groups of 4 cells. The last group will be BD5:BG5.
I tried a script like this but it says that there is a syntax error on the "Else" instruction:
Range(Range("AB5").End(xlToRight).Offset(0, 1), Range("AB5").End(xlToRight).Offset(0, nrCat)).Select
VB:
Dim i As Long
nrCat = Range("O2").Value
For i = 1 To nrCat
If Range("AB5") = "" Then
[Code] .....
View 9 Replies
View Related
Feb 18, 2014
I have a spreadsheet with 4 columns.
A, B, C, D
I want to multiply A*B and then add that to C*D
=SUM(A*B)+(C*D)=TOTAL
However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.
Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.
Also - when the total cell is empty I want it blank.
View 13 Replies
View Related
Aug 17, 2009
I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
View 5 Replies
View Related
Jan 10, 2014
Solve the following error: Run-time error '1004': Method 'Evaluation' of object '_Global' failed
My VBA code:
Sub Difference()
For i = 1 To 23
Sheets("Sheet" & i).Select
[Code].....
View 9 Replies
View Related
May 15, 2014
Say I have the number 325 in A1. Each time I type a number in A2, B2, C2, D2, E2, A3, B3, etc... I would like the number (325) in A1 to be reduced.
Also, how do I create a pull down list?
View 6 Replies
View Related
Oct 19, 2012
I have a spreadsheet of clients' body weights each week which goes in a column and to the right of it I have a "diff" which I want to show weight gain/loss.
So for a client with weigh-ins the week of 10/1, 10/8, and 10/15 the columns will be:
10/1(V1)--Diff(W1)--10/8(X1)--Diff(Y1)--10/15(Z1)...and so on.
If I have weigh-ins for each week, no problem. I would just have to: =SUM(X1-V1) to get the change in weight from the week of 10/1 to 10/8. Say the client weighed 200 on 10/1 (V2) and 195 on 10/8 (X2), the difference (or "diff" (Y2)) would be -5.
But let's say the client didn't weigh-in on 10/1. The difference in weight is going to be 195. Or if they weighed-in on 10/1 but didn't on 10/8 it's going to read -200.
So I have two cells (say V2 & X2) that if one of them is blank, I want the "diff" to read "N/A" or at least leave the cell blank.
View 2 Replies
View Related
Dec 10, 2013
Here is what I have:
A B C D
Date ReceivedApproved DateDenied DateTurn Time
1/1/14 1/13/14 9
Turntime is calculated with this formula:
=IF(A4<>"",IF(COUNT(B3,C3),NETWORKDAYS(A3,IF(B3<>"",B3,C3)),""),"")
What I need is to not count the first day it is received in the calculation for the Turn Time. So in this instance I need the Turn Time to say 8 instead of 9. If I just subtract a 1 then I get an error if the Approved Date or the Denied Date are not filled in yet.
View 4 Replies
View Related
Mar 11, 2011
I had a question considering a workbook that I am working on for overtime, a different one than I posted before. On my workbook, I have vacation leave, sick leave, and overtime to figure in. I want to be able to have a choice to choose which you would like to keep at the end of the week. Such as if you were to work 8 on Mon, 8 hours on Tues, 10 hours on Wed, and then take vacation leave on Thur, for 8 hours, and then work 6 hours on friday, I need a way to choose whether to be paid for the overtime and vacation time, or to subtract two hours from my overtime and add it to my regular time, i.e. 40 hours, or subtract two hours from my leave time and add it to my regular time. I know this sounds complicated, it is at least to me.
View 3 Replies
View Related
Dec 11, 2009
I was trying to subtract two arrays of data and putting the result in some cells but with no success unfortunately. I'm relatively new to VBA and I'm just starting now to make calculations with arrays so excuse my little knowledge. The arrays that I'm trying to subtract are from row 1 to 250 and m and n variables have the number of the columns. Here is my routine:
View 3 Replies
View Related
Oct 2, 2013
I have a drop down list with 'Weekly', '4 Weekly' or 'Monthly' as the selectable values.
I want the cell below to calculate a number in an adjacent cell with either 52 for weekly, 13 for four weekly or 12 for monthly.
I.e 100 and weekly would display = 5200 ( 100 x 52 )
100 and monthly would be 1200 = ( 100x12 )
View 2 Replies
View Related
Jun 8, 2009
I have been trying to multiply cells(6,3) of sheet 'Input2' with the 23rd row of next sheet 'Input' from 4th column in that row to last used column. Then I want this looping to go from 23rd row to the last used row in that sheet. But I am not successful. The code that I tried till now is:
Sub test()
Sheets("Input").Activate
x = Range("D23").End(xlToRight).Select
For j = 4 To x
m = Sheets("Input2").Cells(6, 3) * Sheets("Input").Cells(23, j)
Sheets("worksheet").Activate
Sheets("worksheet").Cells(j, 17) = m
Next j
End Sub
View 9 Replies
View Related
Apr 7, 2014
Basically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e
Sheet 1
100
Sheet 2
2,5,9,15,20....
I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....
I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.
View 3 Replies
View Related
Feb 9, 2010
I have these two formulas I am trying to multiply
=ROUNDDOWN(AK170/Sheet1!B3,0) Returns a value of 2
=ROUNDDOWN(AL170/Sheet1!C3,0) Returns a value of 7
I need to multiply these 2 formulas together to return a value of 14
View 2 Replies
View Related
Jan 6, 2008
I have tried to put together three simple formulas for a very simple problem. I wanted to string together three vlookup formulas which were as follows;
=vlookup(F4,prices,6)+vlookup(D4,floorpremiums,2)+vlookup(G4,aspectpremiums,2)
The first lookup would work but as soon as I added another it would result in #n/a. I tried adding the suffix "false" after column no. but that did not work.
On the second lookup I did mix text and numbers e.g the first row in the column had a "G" in it and the rest were numbered 1-10. The second two vlookups were on a seperate sheet but in the same workbook.
I have checked the named ranges and they work fine when done individually but it seems to be the combining which messes things up.
View 9 Replies
View Related
Aug 15, 2006
I need to multiply the values of 10 rows by the values in 10 columns and sum then, akin to the sumproduct function, which only seems to work if the values are all in rows or all in columns.
In other words;
rows of 1 2 3.......10 & columns of
a
b
c
.
.
j
Expressed as (1*a) + (2*b) + (3*c) + ....... (10*j)
I can do it long hand and I can do it by transposing the data as an intermediate step, but I was looking to do it all in one formula.
View 6 Replies
View Related
Mar 21, 2014
I am creating a spreadsheet for some deliveries. The pieces I have are all in feet and inches and multiplied by a certain quantity. I need a formula to run this and give me feet as an answer.
I have 4 cells, in A1 I have feet without the foot mark (20) and in cell B1 I have inches without the " (5). In cell C1 I have 20. How do I write a formula to do the following:
20'-5" multiplied by 20 = Answer in D1 408'
View 2 Replies
View Related
Jan 29, 2013
I would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.
SKuIDItemNameCustomerRate
11682Item 1Cust 1312.56
11682Item 1Cust 2411.27
11682Item 1Cust 3411.27
[code]....
View 2 Replies
View Related
May 30, 2014
I have to complete the following project in visual basic for Excel
direction quantity price
b 400 10
b 100 20
s 900 15
So the above is a small sample space of what i have(over 3000 rows in total) they are stocks. if there is a "b" in the direction that denotes a negative (short) and if there is an "s" (long) that denotes positive.
Two parts:
1) Write a traditional macro (with button) that when pressed, will give the total Net value in a message box.
2) Secondly, write the code in 'class module' that give me the over all net value in a message box.
I understand what I need to do but just don't know how to write the code for this in visual basic.
So for the above example the net would be -400(10) -100(20) + 900(15) = +$7500
But how can I write a program to do this and display the final answer in a message box?
Exercise.xlsm
View 14 Replies
View Related
Sep 22, 2013
I need a formula for H2 to multiply the value if it is in any one of the cells in B2 - B6 by the appropriate value to calculate the annual total. Such as if the number is in weekly column it would multiply by 52, fortnightly 26, monthly 12, quarterly 4 and annually 1.
Weekly
Fortnightly
Monthly
Quarterly
Annually
Ann Totals
Mortgage
1000.00
Council Tax/Water/Sewerage
1000.00
View 2 Replies
View Related
May 6, 2008
What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?
View 4 Replies
View Related
Sep 1, 2008
I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.
I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.
If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.
For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.
View 12 Replies
View Related
Jun 3, 2014
I am trying to put together a basic sheet with what I think is basic math, however, the multiplication does not seem to be working properly.
Field 1 is land value, field 2 is total acres. The math I have in field 3 is to give me a value per acre. Here is that formula: =Sum(B3/C3)
I have set the field to a number field with two decimal places.
Field 4 is Total Dry Acres field 4 is calculated to give total value for dry acres (here is where I am having problems) Here is the formula: =Sum(D3*E3)
Here are the numbers: Field 1 = 218702 Field 2: 1536.51 Field 3 (calculated): 142.34 Field 4: 1171.75 Field 5: 166791.54
It all looks good on the spreadsheet, however the field 5 number is not correct.
Break it out on a calculator: 218702 / 1536.51 = 142.34 (rounded)
Now 142.34 * 1171.81 = 166795.44
so working on the calculator, there is a difference of 3.69 between the two.
View 5 Replies
View Related
Jun 4, 2006
I have a spreadsheet of measurements which i need to convert. In short, all of the numbers must be multiplied by 2.54. I can follow how to do 1 number at a time, but the sheer number of calculations makes this highly inefficient. I think my problem lies in settng up the formula properly. I am working with an iBookG4 with an excel program 11.2.3.
View 3 Replies
View Related
May 22, 2014
i have created this spreadsheet for windows and doors. My only problem right now is that when i put a quanity of "3" in the correct column with frame removal. it doesn't multiply it by 3.
Example:
90x90 = 56.25 sq ft (window) : 3 QTY .. with frame removal should equal $ 855 but its only adding the 1 frame removal so its $785
Frame removal, jamb ext and casing should multiple the value from the table worksheet with the QTY of windows.
View 2 Replies
View Related
Nov 10, 2008
I know when you multiple anything by 0 the answer is 0.
But I have a list of numbers I need to multiply out
but want to keep the total even if there is a 0 in the sequence
Eg
A1*A2*A3*A4
10*15*0*11
5*21*6*0
0*14*10*0
So A1*A2=150 if I then multiply by A3 I get 0, but I need to ignore the cells that have 0.
So for the last example my answer would be 140.
View 9 Replies
View Related
Nov 13, 2008
I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.
Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.
The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).
*I'm running Excel 2003 on WinXP Pro
View 9 Replies
View Related
Mar 3, 2014
I run a report from a scheduling software that gives me the total hours and minutes for a weekday for my bus routes. For instance, 253 hours and 23 minutes for one day. Now though, I need to calculate how many hours and minutes for the entire month. The report will only come out with 253h23. I need to somehow take that, turn it into time and then multiply it by 20 (January weekdays). But I need real hours and minutes and I'm having trouble getting them.
View 7 Replies
View Related
Feb 16, 2012
I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?
For example I use =IF(B2
View 9 Replies
View Related
