IF Statements Help - DESPARATELY NEED HELP!!

willlobb · Apr 6th, 2002, 04:38 AM

Is there a limit on how many IF's you can have in an IF statement. after about seven, the formula doesnt work. if there is a limit, is there any other option? can i use something like the Case function? or can u run a macro from changing the contents of a cell?
Thanks, Will

[ This Message was edited by: willlobb on 2002-04-06 03:49 ]

Chris Davison · Apr 6th, 2002, 05:25 AM

Just the 7 mate, then it faints

Chris Davison · Apr 6th, 2002, 05:28 AM

other options inculde the use of =AND, =OR and =VLOOKUP, =HLOOKUP etc etc plus a few more I'm sure will get posted after this message

willlobb · Apr 6th, 2002, 05:30 AM

ok thanks chris. can i run a macro when i click a hyperlink, or when the cell contents change?
Thanks, Will

Chris Davison · Apr 6th, 2002, 05:32 AM

I believe both are possible, but would have to do a search to find out how as that's not my area of "expertise".... have you tried a search : I'm sure there's loads of each of these cases

Derek · Apr 6th, 2002, 05:58 AM

Hi Will

Don't know about hyperlinks, but try this event macro:

Right click sheet tab, left click View Code and paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then Application.Run "Message"
End Sub

Create a normal macro called "Message" with this code

MsgBox "Your macro is running"
End Sub

Now when you change anything in cell D4 it will trigger your message macro.

Hope this helps

regards
Derek

Aladin Akyurek · Apr 6th, 2002, 06:12 AM

You can perhaps tell for what kind of task you seem to need more than 7 IFs along with a sample data and expected results.

willlobb · Apr 6th, 2002, 06:30 AM

Thanks for those other replies guys. I am making a financial computer system for a company and i have a table, 3 comlumns wide. the first column is for products sold, the second for quantity and the third for income form each product sold. the third column cotaind the formula:

=IF($D15="Bracelet Thin",$E17*0.5,IF($D15="Bracelet Thick",$E17*1,IF($D15="Bracelet Custom",$E17*1.5,IF($D15="Mouse Mats",$E17*2,IF($D15="Pebble Small",$E17*0.5,IF($D15="Pebble Large",$E17*1,IF($D15="Tree",$E17*4,IF($D15="Wendy Bell Ticket",$E17*5,""))))))))

Column D being the Product sold and E being the Quantity. I need to add more products but when i do the formula doesnt work, because like Chris said, it can only take seven.
Is there any thing i can do. Will a VLookup help? i dont really know how to use one, so if it would work, would you kindly tell me how to do it?
Thank you very much.
Will

Jay Petrulis · Apr 6th, 2002, 07:28 AM

Quote:

On 2002-04-06 05:30, willlobb wrote:
Thanks for those other replies guys. I am making a financial computer system for a company and i have a table, 3 comlumns wide. the first column is for products sold, the second for quantity and the third for income form each product sold. the third column cotaind the formula:

=IF($D15="Bracelet Thin",$E17*0.5,IF($D15="Bracelet Thick",$E17*1,IF($D15="Bracelet Custom",$E17*1.5,IF($D15="Mouse Mats",$E17*2,IF($D15="Pebble Small",$E17*0.5,IF($D15="Pebble Large",$E17*1,IF($D15="Tree",$E17*4,IF($D15="Wendy Bell Ticket",$E17*5,""))))))))

Column D being the Product sold and E being the Quantity. I need to add more products but when i do the formula doesnt work, because like Chris said, it can only take seven.
Is there any thing i can do. Will a VLookup help? i dont really know how to use one, so if it would work, would you kindly tell me how to do it?
Thank you very much.
Will

Hi Will,

Definitely consider a Lookup table. There you list *all* the possible items and then use the lookup functions to return the price factor associated with the item. This board is filled with lookup requests so it should give you a good start.

Specifically for your nested IF statement, you cut 2 IFs by using thr OR statement for the items with equal price factors (Bracelet Thin and Pebble Small; Bracelet Thick and Pebble Large).

You can also circumvent the 7 IF limit by using booleans as in:

=(OR(D15={"Bracelet Thin","Pebble Small"})*0.5+OR(D15={"Bracelet Thick","Pebble Large"})+(D15="Bracelet Custom")*1.5+(D15="Mouse Mats")*2+(D15="Tree")*4+(D15="Wendy Bell Ticket")*5)*E17

This example can be extended and illustrates the use of the OR function within the larger formula.

Another way to overcome the 7 IF limit is to group IF statments
=IF(first 7 IFs,true,0)+IF(second 7 IFs,0)+.....

The best bet for flexibility is to use the lookup functions (VLOOKUP, HLOOKUP, LOOKUP, and INDEX/MATCH).

HTH,
Jay

Chris Davison · Apr 6th, 2002, 11:41 AM

http://www.mrexcel.com/board/viewtop...4201&forum=2&2

try this for your hyperlink / macro enquiry..... courtesy of Mark O'Brien

Apr 6th, 2002, 05:28 AM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	other options inculde the use of =AND, =OR and =VLOOKUP, =HLOOKUP etc etc plus a few more I'm sure will get posted after this message __________________ :: Pharma Z - Family drugstore ::

Apr 6th, 2002, 05:32 AM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	I believe both are possible, but would have to do a search to find out how as that's not my area of "expertise".... have you tried a search : I'm sure there's loads of each of these cases __________________ :: Pharma Z - Family drugstore ::

Apr 6th, 2002, 11:41 AM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	http://www.mrexcel.com/board/viewtop...4201&forum=2&2 try this for your hyperlink / macro enquiry..... courtesy of Mark O'Brien __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 6th, 2002, 04:38 AM	#1
willlobb Board Regular Join Date: Mar 2002 Posts: 103	Is there a limit on how many IF's you can have in an IF statement. after about seven, the formula doesnt work. if there is a limit, is there any other option? can i use something like the Case function? or can u run a macro from changing the contents of a cell? Thanks, Will [ This Message was edited by: willlobb on 2002-04-06 03:49 ]

Apr 6th, 2002, 05:25 AM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Just the 7 mate, then it faints

Apr 6th, 2002, 05:30 AM	#4
willlobb Board Regular Join Date: Mar 2002 Posts: 103	ok thanks chris. can i run a macro when i click a hyperlink, or when the cell contents change? Thanks, Will

Apr 6th, 2002, 05:58 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Will Don't know about hyperlinks, but try this event macro: Right click sheet tab, left click View Code and paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$4" Then Application.Run "Message" End Sub Create a normal macro called "Message" with this code MsgBox "Your macro is running" End Sub Now when you change anything in cell D4 it will trigger your message macro. Hope this helps regards Derek

Apr 6th, 2002, 06:12 AM	#7
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	You can perhaps tell for what kind of task you seem to need more than 7 IFs along with a sample data and expected results.

Apr 6th, 2002, 06:30 AM	#8
willlobb Board Regular Join Date: Mar 2002 Posts: 103	Thanks for those other replies guys. I am making a financial computer system for a company and i have a table, 3 comlumns wide. the first column is for products sold, the second for quantity and the third for income form each product sold. the third column cotaind the formula: =IF($D15="Bracelet Thin",$E170.5,IF($D15="Bracelet Thick",$E171,IF($D15="Bracelet Custom",$E171.5,IF($D15="Mouse Mats",$E172,IF($D15="Pebble Small",$E170.5,IF($D15="Pebble Large",$E171,IF($D15="Tree",$E174,IF($D15="Wendy Bell Ticket",$E175,"")))))))) Column D being the Product sold and E being the Quantity. I need to add more products but when i do the formula doesnt work, because like Chris said, it can only take seven. Is there any thing i can do. Will a VLookup help? i dont really know how to use one, so if it would work, would you kindly tell me how to do it? Thank you very much. Will