Add +1 for last 5 years’ sales #s

deirdreob

New Member
Joined
Sep 15, 2013
Messages
12
I'm getting all twisted up in my logic for the following situation, or maybe this can't be done with a simple formula.... Thank you!
In B3, add +1 for each year up to 5 that company has had sales and they have not declined; also add +1 for each year company’s sales have increased.

A B C D E F G H I
1 YEAR 2013 2012 2011 2010 2009 2008 2007
2 SALES 500 250 100 0 100 50 20
3 SCORE __
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
It probably can be done with a formula, but the logic is not completely clear to me.

Can you tell us what the "SCORE" should be for that example ?
 
Upvote 0
It probably can be done with a formula, but the logic is not completely clear to me.

Can you tell us what the "SCORE" should be for that example ?
====================
Thnx Gerald, "Score" is the sum of the points.
In parsing the Rqmts into my Logic, have I captured what is needed (are the two underlined statements the same) ... or am I too tired?
Rqmt: "add +1 point for each year up to 5 that the company has had sales and they have not declined"
--- Logic: ... company's current sales are greater than zero (or <> 0), and not less than the previous year
Rqmt: "add +1 point for each year up to 5 that company’s sales have increased"
--- Logic: ... company's sales are are greater than the previous year
 
Upvote 0
OK, so what should the actual numbers be ?

Are the underlined statements the same ? It seems to me that they ARE effectively the same.
But to be certain, what I would try and do is check this with the person who set these requirements.
Maybe they meant them to be different, but phrased them slightly differently from what they meant.
 
Upvote 0
G - Not possible to clarify the Rqmts. The sales are figures are (from most recent backward; displayed in a row): 500 250 100 0 100 50 20 ....
Thanks! D
 
Upvote 0
Not possible to clarify the Rqmts.
Really ? Then you're going to have to guess. I've given you my guess :)

The sales are figures are (from most recent backward; displayed in a row): 500 250 100 0 100 50 20 ....
Thanks! D
Sorry, I think you misunderstand me.
What I mean is, what do YOU think the answer should be in this case ?
 
Upvote 0
Why 8 exactly ?

It looks to me like it might be 10.
---------------
I have contacted the author for a rqmts clarification.
You're right; it's 10: To evaluate 5 years of data, the periods from 2013 - 2006 must be used, therefore 6 comparisons are made, of which only 1 did not meet all of the rqmts.

Sales values for 2013 - 2008 span B2 - G2.
The copied formula =IF(AND(B2<>0,B2>C2),2,0)
and displayed Scores span B3 - G3.
I sum the Scores in B4.
The second to last argument of the formula displays a 2 when both conditions are true - as the two "increasing sales" rqmts appear to be the same. (Hopefully author will generously clarify this.)

Nesting If(and statements is not one of my strong points, but it would be preferable to summing the individuals
Thanks for hanging in there! Stay tuned for an update, D
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top