Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

can this be added to formula?

Posted by Jim on June 15, 2001 9:42 AM

Hello,
I have a formula:
=sum(G6*B17*A17,If(E28=E17,-G28),(If(E29=E17,-G29*B29)))
What i need to do is tweak it with:
If B17=1 then A17*G17 or
If B17>1 then A17*B17
Any thoughts?
Jim

Re: can this be added to formula?

Posted by g on June 15, 2001 9:44 AM

Re: can this be added to formula?

Posted by Russell on June 15, 2001 9:45 AM

What exactly do you need to tweak - the first part (G6*B17*A17)?

Re: can this be added to formula?

Posted by Tuc on June 15, 2001 10:01 AM

Something like
=Sum(G6* (IF(B17=1, A17*g17, IF (B17>1, A17*b17, b17))), If(E28=E17,-G28),(If(E29=E17,-G29*B29)))

Re: can this be added to formula?

Posted by thomas venn on June 15, 2001 12:10 PM

your formula is not correct for Excel. your formula needs to be like this:

=if(b17=1,a17*g17,if(b17>1,a17*b17,if(e28=e17,-g28,if(e29=e17,-g29*b29,b17))))

I am not quite sure what you are trying to do with the "Sum(g6*". The point here is, that you must use a new IF statement for each argument. you may have up to seven IF THEN statements, but no matter how many IF THEN statements you make, you can have only one ELSE statement.

Cheers,

-thomas

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.