Multiplying the contents of a sum of cells by a number in another cell designated by a code in another cell.
For the purposes of explanation, K22 is $500.00
K23 is (125.00)
K24 is (50.00)
I want insert codes 1 to 9 in J25 which causes the sum of K22:K24 to be mulitplied by a value in AA1, AA2, AA3...AA9 with the result being in Z25.
StatementTest2.xls
K23 is (125.00)
K24 is (50.00)
I want insert codes 1 to 9 in J25 which causes the sum of K22:K24 to be mulitplied by a value in AA1, AA2, AA3...AA9 with the result being in Z25.
StatementTest2.xls
Assuming the 3 in J25 means you want the third item in list AA11:AA19
=IF(K23<>0,SUM(K22:K24)*IN
Thanks
Rob H
=IF(K23<>0,SUM(K22:K24)*IN
Thanks
Rob H
ASKER
Rob, you formula works great. However, since the formula will be in the cell in each line and rarely active, I need the result to be blank or a non showing 0 in order for the running balance in column V to continue to work. It goes to $VALUE! when the formula is in cell and unactive.
ASKER
On second thought, maybe I could modify the running formula in column V to ignore the contents of that cell when the cell is dormant.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everything quit working and K25 with a value in J25 shows #NAME? as does K26 with No value in J26
ASKER
Sorry, using Excel 2003 which I do not believe supports IFERROR
Why not add another IF statement to Rob's formula. E.g:
This checks if the J column is empty and only does the lookup if it is not.
=IF(J23="",0,IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))This checks if the J column is empty and only does the lookup if it is not.
ASKER
JEaston, that is returning a 0 to K 25 now matter whether there is a value in J25 or not?????
Sorry, I didn't check the row number. It should have been J25="". Therefore:
=IF(J25="",0,IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))ASKER
Never mind. Now its working but showing a "0" in K whatever when the corresponding cell in J is empty. Can we get rid of the 0
ASKER
I caught the row number, hence my last response. Still trying to figure out the 0
Of course. Just replace the 0 after J25="",0,
So to output a blank cell, you would need:
So to output a blank cell, you would need:
=IF(J25="","",IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))ASKER
The next line, with no value in J26 reads
=IF(J26="",0,IF(K24<>0,SUM
now returns 0.00
=IF(J26="",0,IF(K24<>0,SUM
now returns 0.00
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I see the change. That works, but now my running total in V goes to #VALUE! on that line and every line thereafter. I am not sure how to rewrite the formula in V, which currently is =SUM($V25+$K26-$N26) to ignore the contents of K if K is not a number.
This appear to be due to column K referencing column Z. If you change the formula in K from =IF(Z25<>0,Z25,"0") to =IF(Z25<>"",Z25,"0") it should resolve the issue. Obviously update the row numbers depending on what row has the error.
ASKER
I got it by rewriting the formula in V to
=IF($K27<>"",SUM($V26+$K27
=IF($K27<>"",SUM($V26+$K27
ASKER
Thanks Guys. Going to go take more drugs for my hyperextended knee. May be in LaLa land for awhile.
Glad I could help.
ASKER
In anticipation of my next question...Is there a function that PLACES the result or contents of one cell into another cell without the destination cell containing a formula?
ASKER
Never mind, maybe I should open up the next question instead.
The formula in Z25 should be:
Open in new window