Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last value & if,then
Example
A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#2
|
|||
|
|||
Last value & if,then
Can you add a (hidden) helper column? That column would do essentially just
what you said: pick up the new value in column A or continue the old value is column A is blank. In C1, =A1. In C2, =if(isblank(a2),c1,a2). In D1, =B1*C1 and in D2, =B2*C2. Then just copy down the formulas in C2:D2 to complete your table. --Bruce "Angie A." wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#3
|
|||
|
|||
Last value & if,then
One way
=B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*ROW($A$1:A 1))) After you type it in, hit Control+Shift+Enter instead of just Enter as this is an array formula. Then copy down column C "Angie A." wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#4
|
|||
|
|||
Last value & if,then
Hey- thanks a lot!
= ) "JMB" wrote: One way =B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*ROW($A$1:A 1))) After you type it in, hit Control+Shift+Enter instead of just Enter as this is an array formula. Then copy down column C "Angie A." wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#5
|
|||
|
|||
Last value & if,then
you tell this to C2:
=IF(A2="",C1/B1*B2,A2*B2) then drag em all d way down... "Angie A." wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#6
|
|||
|
|||
Last value & if,then
You're welcome. In retrospect, I would suggest using this slightly modified
version. The last one will result in an error if your table does not begin in Row 1 (so if you move it or insert a row in cell A1 - the last formula will result in error whereas this one would not). Sorry for the confusion. =B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*(ROW(INDIR ECT("1:"&ROWS($A$1:A1)))))) "Angie A." wrote: Hey- thanks a lot! = ) "JMB" wrote: One way =B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*ROW($A$1:A 1))) After you type it in, hit Control+Shift+Enter instead of just Enter as this is an array formula. Then copy down column C "Angie A." wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#7
|
|||
|
|||
Last value & if,then
Hi!
Try this: =LOOKUP(9.999999999999E+307,A$1:A1)*B1 Copy down as needed. Biff "Angie A." <Angie wrote in message ... Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#8
|
|||
|
|||
Last value & if,then
Insert a row before the current data and enter a 0 in A1.
In C2 enter & copy down: =LOOKUP(9.99999999999999E+307,$A$1:A2)*B2 Angie A. wrote: Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
#9
|
|||
|
|||
Last value & if,then
Hi Biff- This worked great!
Thanks! = ) Angie "Biff" wrote: Hi! Try this: =LOOKUP(9.999999999999E+307,A$1:A1)*B1 Copy down as needed. Biff "Angie A." <Angie wrote in message ... Example A B C 7 4 =$A$1*B1 5 =$A$1*B2 6 =$A$1*B3 3 1 =$A$4*B4 4 =$A$4*B5 Isn't there a way to tell Excel to always multiply A*B, without using the $A$? The reason I ask is we have hundreds of sections with new A values and I don't want to put a new $$ formula in for each section. I was thinking of something along the lines of A*B, if A=blank use last A value- but I can't find any examples of this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have "IF,Then" to get data from multiple columns--can I nest add'l IF,THENs? | Excel Worksheet Functions |