multiple "if" arguments

[Guest]

I have a workbook with week1,week2,week3,week4 and period
i am trying to input 1 in a3 week 1 for period a3 to show period 1 or input
5 in a3 week 1 for period a3 to show period 2 and so on
is there a formula for period a3 to show what period i am working on just by
inputing 1 or 5 or 9 so forth in week 1 a3?
any help or ideas will be welcome

 

[pinmaster]

Hi,
Try using a VLOOKUP formula instead. Start by creating a tabl
somewhere in your workbook with your input numbers in column 1 and th
corresponding period number in column 2, then:
=VLOOKUP(A3,your table,2,0)

HTH
J

 

[Guest]

I am having a difficult time getting this if formula to work. All of the
individual pieces work fine and three pieces work as a three-argument
statement. When I add the last argument ...
IF(B28="neither",E2+e3+e4+E5+B19+B22+k49 ... I get this error: "You've
entered too many arguments for this function."

Here is the whole formula:

=IF(B28="cash",0,IF(B28="yes",E2+E3+E5+K49,IF(B28="no",E2+E3+E4,+E5+K49,IF(B28="neither",E2+E3+E4+E5+B19+B22+k49))))

 

[Dave Peterson]

Did you mean this:

IF(B28="no",E2+E3+E4,+E5+K49
or
IF(B28="no",E2+E3+E4+E5+K49

 

[Peo Sjoblom]

You have an extra comma here
B28="no",E2+E3+E4,+E5+K49
use
B28="no",E2+E3+E4+E5+K49

You would probably want something like

=IF(B28="cash",0,IF(B28="yes",E2+E3+E5+K49,IF(B28="no",E2+E3+E4+E5+K49,IF(B2
8="neither",E2+E3+E4+E5+B19+B22+K49,""))))

 

[wjohnson]

Why not have the 3 different cells reference the "groups" of cells you
are adding for the different conditions. Then in your formula - make a
reference to the individual cells.
Your formula:
=IF(B28="cash",0,IF(B28="yes",E2+E3+E5+K49,IF(B28=
"no",E2+E3+E4+E5+K49,IF(B28="neither",E2+E3+E4+E5+B19+B22+K49,""))))
Example
Cell A1 contains the following SUM (E2+E3+E5+K49)
Cell B1 contains the following SUM (E2+E3+E4+E5+K49)
Cell C1 contains the following SUM (E2+E3+E4+E5+B19+B22+K49)
Then change the formula as follows:
=IF(B28="cash",0,IF(B28="yes",A1(B28=
"no",B1,IF(B28="neither",C1,""))))
Then I usually highlight the cells with a color to indicate an
"reference" to other cells, so I don't manually enter any text or
numbers into them.

 

[Kris]

Here is the whole formula:

=IF(B28="cash",0,IF(B28="yes",E2+E3+E5+K49,IF(B28="no",E2+E3+E4,+E5+K49,IF(B28="neither",E2+E3+E4+E5+B19+B22+k49))))

^^^^^^^^^^^^
,+E5+K49 ?????

 

[PCLIVE]

You should be able to simplify your formula as follows.

=IF(B28="cash",0,IF(B28="yes",SUM(E2,E3,E5,K49),IF(B28="no",SUM(E2:E5,K49),IF(B28="neither",SUM(E2:E5,B19,B22,K49)))))

HTH,
Paul

 

[PCLIVE]

In your previous formula, it looks like you had a stray comma.

=IF(B28="cash",0,IF(B28="yes",E2+E3+E5+K49,IF(B28="no",E2+E3+E4 ,
+E5+K49,IF(B28="neither",E2+E3+E4+E5+B19+B22+k49))))

 

[Sandy Mann]

Assuming that that others have been saying about the additional comma,
because you are referencing the same set of cell in all three options the
formula can be shortened to:

=IF(B28="cash",0,SUM(E2,E3,E5,K49)+IF(B28="no",E4,IF(B28="neither",SUM(E4,B19,B22),0)))

Note however that it will return SUM(E2,E3,E5,K49) for "yes" or any other
entry in B28 except "no" or "neither" If this is not acceptable then add
another IF before the SUM(E2,E3,E5,K49)

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk