• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Table Fun

B

barmacost

New Member
I've got a data table setup based on transaction pricing. Column headers are fees for incoming transactions and row headers are fees for outgoing transactions. The data table is dynamic based on different volume scenarios in the other part of our model. What I want to do is find some way to dynamically gather the first instances in each column of the data table where the resulting revenue is greater than or equal to a baseline revenue number we've established.

It's easy enough conditionally format the results and I'm sure there is a way to use index and match to find the intersection but I don't know how to do use the baseline revenue number as a reference point to return the first instance that's greater than that number.


Can anyone help with this?
 
You lost me at incoming and outgoing transaction. Could you kindly show me how it looks, then may be i can understand what you need? I can't link the relationship between transaction and revenue implied.


I can only think in the line of using sumproduct to solve your problem.
 
Basically we provide a service and we get paid when we initiate the service and when we complete the service. Below is a rudimentary table of what I mean. Along the top you'll see pricing for initiating the service and along the side is pricing for completing the service. I just multiplied the two to come up with some fake revenue figures. If my minimum target is $100 in revenue I want to know each "initial" pricing combination that will produce $100 or more in revenue. So for first column of $5 there isn't one. Second column would be $10 coming in and $10 going out. 3rd column would be $15 coming in and $10 going out. 4th column would be $20 coming in and $5 going out...etc. etc. So even though the intersection of $20 and $10 would produce more than $100 I only want to know about the first instance. Make sense?


5 10 15 20 25

5 25 50 75 100 125


10 50 100 150 200 250
 
Something like this?

=INDEX(Column_of_interest,MATCH(Baseline_Value,Column_of_Interest)+IF(COUNTIF(Column_of_Interest,BaselineValue),0,1))


The Match function will find first value equal to or less than baseline value. Then, the COUNTIF checks if the exact value was found. If it was, add 1 aka go to next higher value.


Does that help?
 
That's close. That gives me the resulting intersecting value and quite possibly that's how I worded my question but ultimately, what I want is the column and row headers where the intersection takes place. So what price combinations give the results I want? i.e. the price combo of 15 and 10. Basically, how do you tweak what you sent to give me the resulting row header for each column I check?
 
create 2 lists(pull down menu), barmacost. One for header one for rows. use the pulldown menu as the driver for Luke's formula.


Then condition format your column header and row header to change color when the selecter header and row are chosen on the pulldown menu.
 
Thanks Fred. That would work but I want the results to show up on another page and I don't want people to have to select anything. My thought is to have a list of the column headers and then using a formula like Luke provided to determine the corresponding row header where the criteria is met.
 
So, within a table of numbers, you want to find the closest number anywhere in the table where the number is >= baselines value, and then get the row/column headers?
 
Think I got it...just changing the columnar references for each item in list seems to do the trick. Thank you Luke and Fred!


=IFERROR(OFFSET($B$11,ROW(INDEX(J$11:J$34,MATCH($B$9,J$11:J$34)+IF(COUNTIF(J$11:J$34,$B$9),0,1)))-ROW($B$11),0),"None")
 
You must log in or register to reply here.
Back
Top