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
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John EastonDirectorCommented:
Assuming I understand you correctly, I think you should be able to use the Indirect function, although Offset might work too.

The formula in Z25 should be:  
=IF(K23<>0,SUM(K22:K24)*INDIRECT("AA"&(J25+10)))

Open in new window

Rob HensonFinance AnalystCommented:
Assuming the 3 in J25 means you want the third item in list AA11:AA19

=IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1))

Thanks
Rob H
Bill GoldenExecutive Managing MemberAuthor Commented:
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.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Bill GoldenExecutive Managing MemberAuthor Commented:
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.
Rob HensonFinance AnalystCommented:
=IFERROR(IF(K23<>0,SUM($K$22:$K$24)*INDEX($AA$11:$AA$19,J25,1)),0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Everything quit working and K25 with a value in J25 shows #NAME? as does K26 with No value in J26
Bill GoldenExecutive Managing MemberAuthor Commented:
Sorry, using Excel 2003 which I do not believe supports IFERROR
John EastonDirectorCommented:
Why not add another IF statement to Rob's formula.  E.g:
=IF(J23="",0,IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))

Open in new window


This checks if the J column is empty and only does the lookup if it is not.
Bill GoldenExecutive Managing MemberAuthor Commented:
JEaston, that is returning a 0 to K 25 now matter whether there is a value in J25 or not?????
John EastonDirectorCommented:
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)))

Open in new window

Bill GoldenExecutive Managing MemberAuthor Commented:
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
Bill GoldenExecutive Managing MemberAuthor Commented:
I caught the row number, hence my last response.  Still trying to figure out the 0
John EastonDirectorCommented:
Of course.  Just replace the 0 after J25="",0,

So to output a blank cell, you would need:
=IF(J25="","",IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))

Open in new window

Bill GoldenExecutive Managing MemberAuthor Commented:
The next line, with no value in J26 reads

=IF(J26="",0,IF(K24<>0,SUM(K23:K25)*INDEX(AA$11:AA$19,J26,0)))

now returns 0.00
John EastonDirectorCommented:
You've still got the 0 in the formula.  So for J26 it would be:
=IF(J26="","",IF(K24<>0,SUM(K23:K25)*INDEX(AA$11:AA$19,J26,0)))

Open in new window

Bill GoldenExecutive Managing MemberAuthor Commented:
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.
John EastonDirectorCommented:
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.
Bill GoldenExecutive Managing MemberAuthor Commented:
I got it by rewriting the formula in V to

=IF($K27<>"",SUM($V26+$K27-$N27),SUM($V26-$N27))
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks Guys.  Going to go take more drugs for my hyperextended knee.  May be in LaLa land for awhile.
John EastonDirectorCommented:
Glad I could help.
Bill GoldenExecutive Managing MemberAuthor Commented:
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?
Bill GoldenExecutive Managing MemberAuthor Commented:
Never mind, maybe I should open up the next question instead.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.