I need a VBA macro to multiply column E and F and deposit the result in column G. The multiplication should only happen when there is a value in the column E. If there is no value in the column E, then the cell in the G column should stay blank.
View 9 Replies=IF(OR(A1>0,A2>0,A3>0),*2,"")
if A1,A2orA3 is greater than 0 then multpy by 2 or leave the cell blank
I am trying to show a blank cell if the others don't have any figures in there and am using the following formula.
However, in my cell it is showing "#value" instead. How do I get my cell to look "blank" when there are no values in the other cells? Here is my formula
=IF(SUM(A17*D17)>0,SUM(A17*D17),"")
Excel (2000), what formula would I use to accomplish the following?
If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.
I have a row where C5 is the total of C1:C4. What I am trying to do is if C1 or C2 or C3 or C4 has a number value that I can multiply the cell in question by 2 and then add up all of the cells to give a total in C5. Also, I have a name in A1, but when I select E1 and enter =A1 into the cell then return, I get A1 in the cell instead of the name in A1.
View 5 Replies View RelatedI have a value in a cell in Sheet2 (in this case its price per ft), on sheet1 is where I would input the number of feet. So can I multiply from data in cell on Sheet2 from cell on Sheet1and if so....
View 6 Replies View RelatedCan I get a formula for the below specification.
if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010
All to occur in cell G2
Condition 1.
If F2 is > 0 then multiply F2 by D2.
Condition 2 in the same cell.
If the product of F2 multiplied by D2 is > E2 AND the sum of F2 and F1
multiplied by D2 is > 0, then add the product of D2 multiplied by the sum of
F1 and F2 to E2, otherwise input E2
Condition 3 in the same cell.
If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1
multiplied by D2 is > 0, then input the product of D2 multiplied by the sum
of F1 and F2.
Condition 4 in the same cell.
If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0, then 0.
This is the best way I could think of the write out my intention. I think if
I can get the above scenario into a one cell formula that should resolve my
pain.
After formating I need to multiply by 2 . As you can see my Cells.Value = Selection.Value * 2 is not correct. How can I accomplish this?
With Range("I17:I4000", Range("I" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=ReplaceChar(I17)"
.Value = .Value
Selection.NumberFormat = "0.00"
Cells.Value = Selection.Value * 2
End With
I want to use the little dot in down right corner of the cell to copy the cell content and the formula correctly.
But now the problem: every time I use it also copies the cell formation.... Is there a way that it only copies the content, but not the formation?
I'm trying to make a sheet to calculate my taxes. I would like to keep tax rates from previous year(s), and have them applied based on which year I input in a cell..
Let say that in cell B1 I write year 2013
In cell G1 I have year 2013 and in cells G2:G7 I have tax rates from year 2013.
In cell H1 I have year 2014 and in cells H2:H7 tax rates from year 2014
In cell B17 to B20 I would like to make a formula that multiplies cell B11 or B12 with information found in either column G or H based on input in B1.
I have attached a spread sheet as example.
I know I can make a formula based on IF(B1=2013;G*;IF(B2=2014;H*;etc..
I am looking for formula , single cell two numerical values , multiply in different columns
example :
Cell (A1) contain: =350,23+25,59 (two values in a single cell)
request:
in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number
How can i format a column of cells so that if a number is less than 10 for eg that that particular cell is then multiplied by 1000.
View 9 Replies View RelatedI am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies View RelatedI can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedI presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
Cell A1 = 3
Cell A2 = 4
Is there a formula to calculate
(3*4)+(2*4)+(1*4)
I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;
=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies View Relatedin one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies View Relatedusing a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..
View 9 Replies View RelatedI have checked archives, some similiar but not quite what I want.
In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.
How can I multiply let's say A1 to 10 if B1 = 5
or multiply next cell like A2 to 15 if B2 = 10
I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.
e.g
Started
Finishes
12-Nov-13
12-Dec-13
30-Jan-00
09-Nov-13
09-Dec-13
11-Nov-13
11-Dec-13
Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?
The current formula for cells in the "Finished" column is:
=[@Started]+30