CODEWITHSUNDEEP
excelexcel-formula
Glen Newell
Glen Newell

Reputation: 11

IF / Then / And / Increment

Logically I just can't figure out how I want to right this.

So given a value in 3 different locations will either be a 1 or a 0.

Location 1, Location 2, Location 3. I want to see if something is in stock somewhere and if not get it from this location and put it in a position.

so ideally I'm trying to wright something that looks like: I was thinking something like =IF(SUM(A1-A3)>1, increment?, ) but it doesn't like this.

Loc1 Loc2 Loc3  Get it here
   0    0    0  1 <- increment first in the list where all locations show 0 
   1    0    0    <- this would be left blank (meaning its available elsewhere)
   0    0    0  2 <- this would increment from the previous 1

Just trying to figure it out.

enter image description here

Upvotes: 0

Views: 48

Answers (1)

Dominique
Dominique

Reputation: 17533

Do you mean something like this:

enter image description here

For your information, this is the formula:

=IF(AND(B3=0,C3=0,D3=0),"",MAX(E$2:E2) + 1)

The first part is easy: if all location columns are zero, then put an empty string.
The second part is a bit more difficult: take the maximum from the fix cell to "E2" (while being located in "E3", which means take the cell above. So, take the maximum of all cells above, starting at "E$2". Once you have that maximum, add one.

I admit, the formula, with the MAX(E$2:E2) looks confusing, but drag it down, see how "E2" becomes "Ex" and how "E$2" stays "E$2" and you'll understand.

Upvotes: 1

Related Questions