Microsoft Office Forums sumproduct??
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-09-2012, 02:52 PM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9
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
10-12-2012, 11:47 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

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
10-13-2012, 12:54 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,406

A solution using SUMPRODUCT
Attached Files
 Sumprod.xls (7.0 KB, 5 views)
__________________
Please note I am not active on this forum anymore
#4
10-14-2012, 07:41 AM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9

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
10-14-2012, 07:56 AM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9
sumproduct??

p.s. here is sample file
Attached Files
 Book1.xlsx (8.2 KB, 4 views)
#6
10-14-2012, 08:32 AM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9

Colin tried your vlookup again and it works, thanks, but would really like to calculate charges in one go
thanks again
#7
10-14-2012, 08:33 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

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
10-14-2012, 09:23 AM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9

thanks again Colin
#9
10-14-2012, 09:32 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,406

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
__________________
Please note I am not active on this forum anymore
#10
10-14-2012, 10:00 AM
 jer Windows 7 32bit Office 2007 Novice Join Date: Aug 2011 Posts: 9

my apologies Pecoflyer, your understanding is correct now

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Portucale Excel 2 09-12-2012 10:51 AM angie.chang Excel 1 06-18-2012 08:47 AM angie.chang Excel 3 06-14-2012 10:00 AM Berk21 Excel 7 01-15-2012 11:41 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 08:27 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top