How to do I put 2 working formulas from separate cells into 1 cell

[jimiwilliams]

Im very new to spread sheet programs, and i have learnt some formulas however im very confused by this problem.
some notes to help people understand “J3” houses a drop down menu with the options Not Paid, Paid , Not Done, D references my prices column.

in one cell i have this formula =IF(J3=“Not Paid”,(SUM(D3-D3*2)))
and in another cell i have this formula =IF(J3=“Paid”,(INDEX(D3))) (D references my prices column)
these formulas work fine in separate cells but i would really prefer these operations in 1 cell.

i have tried =IF(J3=“Paid”,(INDEX(D3)=IF(J3=“Not Paid”,(SUM(D3-D3*2)))))
but that makes the cell always read “0” no matter what the drop menu selects.

How do i fix this? Im assuming my formula is very wrong but i dont know why, can someone please help me?

[jimiwilliams]

i also tried i have tried =IF(J3=“Paid”,(INDEX(D3),=IF(J3=“Not Paid”,(SUM(D3-D3*2)))))
the comma between “(D3)” and “=if(j3” being the change but that brings up the "err:510 message

[kompilainenn]

what is a target of your spreadsheet? What do you account?

[jbfaure]

You can combine the formula by using OR, AND or XOR logical functions.

Another possibility is to use IFS() function which admit several conditions. Use the function wizard (first button on the formula bar) to understand its syntax.

Additionally you have the help: button Help in the wizard.

[SM_Riga]

IF formula have such syntax: IF(Test expression,What to do if test result is true,What to do if test is false) Formulas parameters are divided by comma (,) or semicolon (;), so everything between these separators are treated as an expression according to formula syntax. As you can see, both of yours formulas

=IF(J3="Not Paid",(SUM(D3-D3*2)))
=IF(J3="Paid",(INDEX(D3)))

have 3rd parameter “What to do if test is false” omitted, so when test expression not true, your formula returns default False value which is 0. So your formula =IF(J3="Paid",(INDEX(D3)=IF(J3="Not Paid",(SUM(D3-D3*2))))) tests if J3="Paid" is true, then returns (INDEX(D3)=IF(J3="Not Paid",(SUM(D3-D3*2))))if so, or returns 0 whet test is false, cause 3rd parameter is omitted. Does not make sense. In your case correct syntax would be:

=IF(J3="Paid",INDEX(D3),IF(J3="Not Paid",SUM(D3-D3*2),0))

Formula above tests if J3="Paid" is true, if so returns INDEX(D3), if not returns IF(J3="Not Paid",SUM(D3-D3*2),0) which evaluates to SUM(D3-D3*2) if true and 0 if false. Remarks: in your case INDEX(D3) is the same as D3 and SUM(D3-D3*2) is the same as -D3 so formula shortens to:

=IF(J3="Paid",D3,IF(J3="Not Paid",-D3,0))

But remember, this formula tests only if J3 value is “Paid” or “Not Paid”, other cases will return 0. You can nest one more IF statement with “Not Done” case, but for more than 2 expected outcomes I would use IFS formula as @jbfaure suggested:

=IFS(J3="Paid",D3,J3="Not paid",-D3,J3="Not Done",value-to-assign)

[jimiwilliams]

Thank you Both for a brilliant explanation, and solution this has taught me alot thank you.