# Add +1 for last 5 years’ sales #s

#### deirdreob

##### New Member
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Gerald Higgins

##### Well-known Member
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 ?

#### deirdreob

##### New Member
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

#### Gerald Higgins

##### Well-known Member
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.

#### deirdreob

##### New Member

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

#### Gerald Higgins

##### Well-known Member
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 ?

#### deirdreob

##### New Member

Really ? Then you're going to have to guess. I've given you my guess

Sorry, I think you misunderstand me.
What I mean is, what do YOU think the answer should be in this case ?

8

#### Gerald Higgins

##### Well-known Member
Why 8 exactly ?

It looks to me like it might be 10.

#### deirdreob

##### New Member
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

#### Gerald Higgins

##### Well-known Member
OK great. I'm not clear, do you still need help on this one ?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,404
Messages
5,853,588
Members
431,602
Latest member
akosikervin

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

### Which adblocker are you using?

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

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