A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Sum Product Matrix



 
 
Thread Tools Display Modes
  #1  
Old March 13th 17, 07:52 PM posted to microsoft.public.excel.worksheet.functions
Phycodes
external usenet poster
 
Posts: 2
Default Sum Product Matrix

Hi all,

Trying to get a formula to return the following:

Column A: Title
Column B: Salary 1
Column C: Salary 2
Column D: Salary 3
Column E: Salary 4

Based of of this matrix if Employee n's salary falls in between one of the 4 criteria, then I would like the higher value returned

Example:

A: Manager
B: 1000
C: 5000
D: 10000
E: 15000

My lookup index: Title - Manager / Salary - 8000.

I want this to give me the value in column D. If the salary was increased to 11,000 then column E.

So far I am stuck at:

=sumproduct(--('TitleInput'=$A:$A),--($B:$B<'SalaryInput'),--($C:$C<'SalaryInput'),--($D:$D<'SalaryInput'),($E:$E))

Where I only get a returned value if the Salary input is larger than D and Smaller than E. If I make the value D or smaller I get 0.

Thanks!
Ads
  #2  
Old March 14th 17, 01:12 AM posted to microsoft.public.excel.worksheet.functions
Phycodes
external usenet poster
 
Posts: 2
Default Sum Product Matrix

I have also tried:

{=INDEX($A:$E,MATCH(1,(A:A=K9)*(IF('SalaryInput'>D 2,E2,IF('SalaryInput'>C2,D2,IF('SalaryInput'>B2,C2 ,B2)))),0),4)}

but get an #N/A error
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
where can i get a product compliance matrix template from? Nacer Excel Discussion (Misc queries) 0 August 13th 09 02:58 PM
I need a product key for my Trail product, 2007 Microsoft Office s sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM
Chose a product and update related product variables hupjack Excel Programming 0 April 9th 09 11:57 PM
Extracting data from a product matrix tarheelfan Excel Discussion (Misc queries) 0 March 25th 09 08:58 PM
Vlookup code product and to copy commentary with photo of the product in vba [email protected] Excel Programming 0 October 2nd 06 03:54 AM


All times are GMT +1. The time now is 01:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.