Hello Excel forum.
I'm looking for a formula that will multiply the $ Amounts in C2-C14 by their correlating Benefit % in B17-B117 based on the ages of each person found in B2-B14.
I'm looking for a formula that doesn't need any helper rows.
Attached is the sample workbook.
Thanking you in advance...
I can do this with a helper (which you can hide)...
C2=C2*VLOOKUP(B2,$A$17:$B$117,2,0)
copied down
Then sum C
I will see if I can figure out a 1-step formula
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks much. I have a helper row solution in hand already, but I wanted to know if there was a 1 step solution, so if you can find one, that's be great!
Try this...
=SUMPRODUCT(LOOKUP(B2:B14,A17:B117)*C2:C14)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the assist Tony.
How come
=SUMPRODUCT(LOOKUP(B2:B14,A17:B117)*C2:C14)
gives 510 000
but
=SUMPRODUCT(VLOOKUP($B$2:$B$14,$A$17:$B$117,2,0)*$C$2:$C$14)
gives 395000
and the array version of the vlookup gives 790000?
That would work except that my data range has formulas in it, and some of the formulas in my data range are set to show a blank cell, so "", and this causes your formula to error. any work around if some of the $ Amount cells actually have a hidden formula that shows a blank cell ("")?
Can you change the initial formulas to show 0, rather than ""
The normally entered version will return a different result depending on where the formula is entered.
If you enter the formula on rows 2:14, then the lookup value is taken from the same row of column B. For example, if the formula is entered in E7 then the lookup value is taken from cell B7 which is 72.
Then:
VLOOKUP(72,range,0) = 50%
Then:
SUMPRODUCT(50% * C2:C14) = 395000
The array entered version is using the single cell B2 as the lookup value.
VLOOKUP(24,range,0) = 100%
SUMPRODUCT(100% * C2:C14) = 790000
VLOOKUP doesn't return an array to a single cell.
I can't tell who you're replying to.Originally Posted by bwmuhich
Do you mean some of the cells in the range B2:C14 might have formula blanks?
wow, same absoluted formula, copied down...
J2=SUMPRODUCT(VLOOKUP($B$2:$B$14,$A$17:$B$117,2,0)*$C$2:$C$14)
regular formula, copied down
Maybe this array formula**:
=SUM(LOOKUP(B2:B14,A17:B117)*IF(ISNUMBER(C2:C14),C2:C14))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
@TONY - I tried the Array formula alternative, and unfortunately, my actual worksheet also includes formulas in the Age column range B2-B14, so some cells have a programmed blank ("") instead of an actual age, similar to the $ Amount column range (C2-C14).
One other thing, my actual worksheet has multiple $ Amount columns with spaces between them. The Age Column B is a constant in my worksheet, but the formula I would have would need to be able to be copied and pasted to the right of it to account for the actual Benefit % columns that may be different in every row that I need the formula.
So the constant columns/ranges include: B2-B14, C2-C14 and A17-A117. The column/range B17-B117 is the moving column that the formula would need to be flexible for.
How about posting another sample file that more closely resembles your requirements.
Include examples of the results you expect.
@TONY. I've attached an updated sample worksheet that has a couple hidden formula "" blank cells.
Just tuning Tony's formula here.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
@JACC. That almost works for my actual worksheet, but in my actual worksheet, I need this formula to be able to be dragged to the right to account for multiple columns of data that I have in my spreadsheet.
I've updated my sample workbook one more time to even more accurately reflect my actual workbook.
Do you know of a consistent formula that can be dragged across to the right to accomplish what I'm looking for?
Be Well.
Something like this then. Small adjustments to formula, it's now dragable to the right.
This formula works for me
=SUMPRODUCT(LOOKUP((0&$B5:$B17)+0,$A20:$A24,C20:C24),$C5:$C17)
Audere est facere
@Jacc & @Daddylonglegs.
Many thanks! I've opted for the non array formula for the time being, but both of these solutions will help me going forward.
Be Well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks