CODEWITHSUNDEEP
openoffice.orgopenoffice-calc
JPB
JPB

Reputation: 600

Assistance with returning value based on cell being populated with number

Hello and thank you for looking at my post. I am a super noob at spreadsheets but need to automate part of a cost spreadsheet in open office calc after my assistant has repeatedly managed to forget to enter the right data. Here we go:

PART 1

Output is in cell D1.

I have a column of cells A1 to A10, if there is a number (any number) in one or more cells from A1 to A3 then I need the D1 to equal cell B1
+ 1. 

If there is also a number in A4 to A6 then this supercedes the previous formula and D1 will now be B1 + 2. 

Lastly if there is a number in A7 to A10 then again all previous code is superceded and D1 is now B1 + 3.

PART 2

I have cells C1 to C3. If D1 equals B1 + 1, C1 will equal D1 if greater than 3, else it will be 3. C2 and C3 must be 0 or empty.

If D1 equals B1 + 2, C2 will equal D1 if greater than 4, else it will be 4. C1 and C3 must also be 0 or empty.

If D1 equals B1 + 3, C3 will equal D1 if greater than 5, else it will be 5. C1 and C2 must also be 0 or empty.

To me this seems almost impossible, I've spent all day trying to learn IF and ELSE statements and ISNUMBER and many other approaches and I've run out of options. Any help or advice would be greatly appreciated and if I haven't been clear please ask me to clarify any point.

Upvotes: 0

Views: 46

Answers (1)

Jim K
Jim K

Reputation: 13790

Find a column where you can put some other formulas, for example column E. Breaking the formula into parts will make it easier.

In E1, put =IF(E4, 3, IF(E3, 2, IF(E2, 1, 0))).
In E2, put =OR(ISNUMBER(A1),ISNUMBER(A2), ISNUMBER(A3)).
In E3, put =OR(ISNUMBER(A4),ISNUMBER(A5), ISNUMBER(A6)).
In E4, put =OR(ISNUMBER(A7),ISNUMBER(A8), ISNUMBER(A9), ISNUMBER(A10)).
Then the formula for D1 will be simply =B1 + E1. This is part 1 in the question.

For part 2, the requirement of "C2 and C3 must be 0 or empty" should not be needed, because they will be empty if D1 equals B1 + 1 anyway. So this is all that is needed:

In C1, put =IF(E1=1, IF(D1>3, D1, 3), "").
In C2, put =IF(E1=2, IF(D1>4, D1, 4), "").
In C3, put =IF(E1=3, IF(D1>5, D1, 5), "").

Upvotes: 2

Related Questions