Ask Your Question

Calc using something like nested IF statements [closed]

asked 2013-08-26 19:24:39 +0200

Guzzlebird gravatar image

updated 2015-10-26 22:08:06 +0200

Alex Kemp gravatar image

I am working on a project and ran into a slight issue, and would love help solving it. The biggest issue is that I am not totally sure what I am asking. I have cells C39 through C45 that need to return values based off of C47 through C67. Basically if C47 is 1 than C39 is 1, if C47 is 2 then C40 is 1, and so on. C47 through C67 can only use values of 1 through 6, and this wouldn't be so hard if each one affected C39-C45 differently. image: The area

I tried using nested IF statements but that was long and complicated, and I don't happen to know other methods of doing this. I have it working using quite a bit of IF statements on a second sheet, but I would like help condensing it to just the first page in those cells.

Addendum: Sorry I forgot to mention, but the value of C39 through C45 need to add one based on the values of C47 through C67. So if C47 and C48 are both 1 then C39 needs to be 2. C39 through C45 can be any number.

Addendum: Sheet 1 is the front-end of the project, and Sheet 2 is where Sheet 1's C39 through C45 are calculated.


Front End C39-45

Top purple is for send to front end, the rest is for when C47-67 are 1:

Rear End C39-45&C47-67

For 2:

When C47-67 are 2

For 3:

When C47-67 are 3

For 4:

When C47-67 are 4

For 5:

When C47-67 are 5

For 6:

When C47-67 are 6

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 22:08:28.523952


Sorry by I do not understand how and what you want achieve. Please can you attach or share a sample file.

m.a.riosv gravatar imagem.a.riosv ( 2013-09-03 00:53:44 +0200 )edit

Here is a cut down version of the full spreadsheet that contains the part's important to what I am trying to do. There are two sheets, the first sheet is where data input and output are with some work in it, and sheet 2 contians all the work for C39 through C67. The idea is to remove the second sheet, and have all the same stuff happen within C39 through C45.

Guzzlebird gravatar imageGuzzlebird ( 2013-09-12 18:09:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-08-26 23:23:10 +0200

m.a.riosv gravatar image

updated 2013-09-12 19:30:56 +0200

For a few values maybe the CHOOSE function can help:

LibreOffice help:
Uses an index to return a value from a list of up to 30 values.
CHOOSE(Index; Value1; ...; Value30)
Index is a reference or number between 1 and 30 indicating which value is to be taken from the list.
Value1...Value30 is the list of values entered as a reference to a cell or as individual values.
=CHOOSE(A1;B1;B2;B3;"Today";"Yesterday";"Tomorrow"), for example, returns the contents of cell B2 for A1 = 2; for A1 = 4, the function returns the text "Today".
" Edited.

Try with this formula in Sheet2.C3, each summand is a sum group in your formula: =(Sheet1.C47=1)+ SUMPRODUCT(Sheet1.C48:C55=1;Sheet1.C22:C29)+ SUMPRODUCT(Sheet1.C$15>{0;1;2;3};12(Sheet1.C$64:C$67=1))+ SUMPRODUCT(Sheet1.C$12>{0;1;2;3};3(Sheet1.C$56:C$59=1)) verify for each one you get the desire result.

edit flag offensive delete link more


The only issue with CHOOSE is that it seems to only return one value. I am using it like this: =CHOOSE(C47;1) The issue with this is that I need to check C47 through C67 for different values. C47 through C55 are the same but others mean different things with different values. Also, when left blank, or with 0, it returns ERR:502.

Guzzlebird gravatar imageGuzzlebird ( 2013-08-30 17:37:02 +0200 )edit

Sorry but I think a more detailed explanation about what you want achieve for every cell, could help to help. Only matrix formulas can return multiples values, but not as result for only one cell.

m.a.riosv gravatar imagem.a.riosv ( 2013-08-30 21:45:13 +0200 )edit

Apologies for not showing the back end. I added images to the OP. Hope that helps.

Guzzlebird gravatar imageGuzzlebird ( 2013-09-02 22:50:51 +0200 )edit

answered 2013-09-04 13:57:30 +0200

erAck gravatar image

You might want to take a look at the SUMIF and SUMIFS functions.

edit flag offensive delete link more


I took a look into SUMIF and it will condense a lot, but I have a slight problem getting it all into a single cell. This would work perfectly but...This is how it turns out and The problem is that some actually need to be multiplied by other cells, and I am unsure of how to do this. Ignore the REF errors in that, they don't mean anything important.

Guzzlebird gravatar imageGuzzlebird ( 2013-09-04 19:38:59 +0200 )edit

Question Tools

1 follower


Asked: 2013-08-26 19:24:39 +0200

Seen: 11,964 times

Last updated: Sep 12 '13