• 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.

How do you make multiple evaluations in one formula?

H

hmoorex3

New Member
In the attached worksheet, for the value in cell C3, I want to project how many of the cities contracted will fall into each of the categories in cells B72- B75. I would then want to multiply the corresponding value in cells C72-C75 and add the four products together to determine the projected amount of licensing fees for the month

• Assume 30% of the cities under contract will meet the criteria established in B72

• 30% will meet B73

• 30% will meet B74

• And finally, 10% will meet B75


Also, is it possible for whole numbers to line up with the accounting numbers? You can see the issue in rows 2 – 10. Or is it supposed to look like that to differentiate between the two types of values?


Hope I explained it well!
 
Hi hmoorex3,


See the Forum Home page and read the 3rd Green Sticky on how to post a sample file, you have posted your last one in the Tags box, but we can't open it :-(
 
After a bit of detective work, the link is:

https://dropbox.com/u/25491363/adaptive%20spreadsheet.xlsx
 
Change your table in B72:C75 from:

[pre]
Code: 
0 - 25K = 	$35,000
25K - 55K = 	$65,000
55K - 85K = 	$90,000
Over 85K =  	$125,000
to

0	$35,000
25000	$65,000
55000	$90,000
85000	$125,000
[/pre]
Then you can use a simple lookup like:

=VLOOKUP(B54,B72:C75,2)

to retrieve the values that correspond to each category
 
Sorry - I read the sticky and thought I was following the instructions but evidently not. How should I have made the file available?


Hui, thanks for the response but I'm not sure I completely follow. Could you possibly walk me through what that formula is going to do?(you lost me with the B54) Again, I'm tryig to project how much in licensing fees I can expect to realize per month. If I have 10 for a value in C3 (cities under contract) then 10 * 30% = 3. So I would then multiply 3 by $35K = 105K. I'm then going to add the four products I arrived at ($105K+$195K+$270K+$125K). This is my value for C7. Question, how do I account for when the numbers don't come out so neatly? Do I round? How does that affect the integrity of the value for C7?


Hope I'm saying all this properly....
 
Hui, were you or someone else interested in helping me with my issue? I am really at a loss for how to arrive at a formula that will give me the results I need.
 
Hmoorex3


A few pointers


You pasted the link to your file into the Tags field, that truncated it and removed the spaces making the link useless.

paste links to files into the body of your post


Don't you simply want:

=(C3*0.3*$C$72)+(C3*0.3*$C$73)+(C3*0.3*C74)+(C3*0.1*C75)

= $695,000 if C3 = 10

As you don't appear to lookup the values in B72:B75 in your comments above


Finally, This site provides free assistance. In the Rules & Etiquette, it reminds readers of this as well as the fact that the world operates 24 hrs a day and that we all have other commitments, families, work etc.

Please refrain from comments like " were you or someone else interested in helping me with my issue? "

If you want to pay someone to help you you are welcome to.
 
If the ratio 0.3, 0.3, 0.3 and 0.1 are constant

You can use this:

=SUMPRODUCT({0.3;0.3;0.3;0.1}*C72:C75*C3)
 
Hui,


Thanks for the insight as to providing links. At first glance, that does appear to be the most logical solution - I will work with it to verify.

Regarding my comments...I didn't mean to offend & I do immensely appreciate the help I get here
 
I've revised to return additional results but have questions:

1) Why in D49 the first evaluation returns 5.4 for D6?

2) How do I write the formula so as to not perform each evaluation if there are too few cities under contract?(see D49 or D50)

2) Why are the figures in row 49 (D-N) so much higher than row 50?

3) What is it called that I am trying to do - that is, what is the excel term?

4) Is there a better way to do what I trying to do?

Thank you


https://dl.dropbox.com/u/25491363/Adaptive%20spreadsheet_r1.xlsx
 
You must log in or register to reply here.
Back
Top