#1
|
|||
|
|||
sumproduct??
hi all, i am not familar with this function however my reading up here and other forums suggest that it might work for what i want, would really appreciate any help. from the below table, if volume = 2549 then charge =2549 * 2.50, will sumproduct work in this instance, if not can you suggest any other function. my first array in the sumproduct is A9<($C$2:$C$6) and i see false, false, true, true, true. i am stuck with the second array to identify 2.5 and then multiply 2549 by 2.50. hope this is clear
From To charge 0 500 3.50 501 999 3.00 1000 2999 2.50 3000 4999 2.00 5000 999999999 1.50 amount to be charged = 2549, let say in A9 , =SUMPRODUCT(A9<$C$2:$C$6) .... |
#2
|
||||
|
||||
If you re-arrange your From-To charge table slightly you can use an approximate match to find the correct charge. Have a look at Example 1 here:
http://colinlegg.wordpress.com/2012/...-with-vlookup/ That example looks up grades by percentage, but it's the same principle. The only reason to use SUMPRODUCT (or equivalent) would be if you want to calculate multiple charges and sum them in one go. |
#3
|
||||
|
||||
A solution using SUMPRODUCT
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
thanks Colin, Pecoflyer for your time and suggestions/feedback.
Colin, yes i want to calculate charges in one, that is, i want to multiply the total in A9 by the associated charge, in the example 2.50. Approximate match does not return correct charge. Not clear on what you mean by re-arranging the order. I tried ascending and descending and neither works Pecoflyer, thanks however formula is not giving the correct answer, so if a9 = 2549, from the charge table it would be 2549*2.5. From To charge 0 500 3.50 501 999 3.00 1000 2999 2.50 3000 4999 2.00 5000 999999999 1.50 |
#5
|
|||
|
|||
sumproduct??
p.s. here is sample file
|
#6
|
|||
|
|||
Colin tried your vlookup again and it works, thanks, but would really like to calculate charges in one go
thanks again |
#7
|
||||
|
||||
You don't need the "To" column, so delete it in your example file. Then you can use either of these formulas:
=VLOOKUP(A9,A2:B6,2)*A9 =LOOKUP(A9,A2:A6,B2:B6)*A9 |
#8
|
|||
|
|||
thanks again Colin
|
#9
|
||||
|
||||
I f i understand correctly, your question was poorly stated.
It is not 3,5 on the first interval + 3 on the second interval (etc..) but 3 starting from 0 if the number is between 500 and 999, etc... Which is, of course a totally different approach
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#10
|
|||
|
|||
my apologies Pecoflyer, your understanding is correct now
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sumproduct formula | Portucale | Excel | 2 | 09-12-2012 10:51 AM |
Match Index with sumproduct/vlookup | angie.chang | Excel | 1 | 06-18-2012 08:47 AM |
Sumproduct | angie.chang | Excel | 3 | 06-14-2012 10:00 AM |
sumproduct formula to pull info from multiple sheets | Berk21 | Excel | 7 | 01-15-2012 11:41 AM |