#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 130
Default IF Dilema

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 120
Default IF Dilema

This may be an easier task using VBA and a Case statement.

http://www.ozgrid.com/VBA/select-case.htm



"Shu of AZ" wrote in message
...
My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27,285
Default IF Dilema

=if(And(DG192=1,DG192<=5),if(,T2<=7.4),Indirect(" C"&DG192+2),"What??"),"")

--
Regards,
Tom Ogilvy


"Shu of AZ" wrote in message
...
My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default IF Dilema

On Sun, 24 Dec 2006 07:23:00 -0800, Shu of AZ
wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7)))))



There are a number of different ways of solving this problem. They vary in
complexity depending on the complexity you require.

Here's one way, given your parameters:

=IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),
CHOOSE(DG192,D3,D4,D5,D6,D7))

You could also set up lookup tables.
--ron
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 80
Default IF Dilema

You do not say what happens if your additional values are true.? You could
do a VLOOKUP, and find values based on that. You could also add "NOT DONE"
after C7, before the first ). In a second cell, you then enter the following
IF formula, starting with =IF(<the cell containing the original
formula<"NOT DONE",<original
location,IF(AND(DG192=1,T27.5),result,IF(AND(DG1 92=2,T27.5),result and so
on. I trust that you mean 7.5, and not 7.4, since <7.5 could equal 7.4,
and 7.4 coul equal 7.5?

--
Hth

Kassie Kasselman


"Shu of AZ" wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 80
Default IF Dilema

Oops, you then hide the cell with the first formula, or set its font to
White, and get your result from the 2nd cell.
--
Hth

Kassie Kasselman


"Shu of AZ" wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 130
Default IF Dilema

Thanks Ron, I tried your formula and received a value error. In the
parameters of the first note is is described showing 5 IF statements:
This formula is in B1.

A cell (b1) is a cell that reacts If t2 is less than 7.4 and DG192 equals 1
and if so, is then populatated with the contents of c3. It continues with
the same if statement except now if DG192 equals 2 it is then populatated
with the contents of c4 and so on until you reach DG192 possibly equaling 5
and populating with c7. The problem is that b1 must also react if t2 is
greater than 7.4 and if so it must look at DG193 instead of DG192 and check
if it is equal to 1,2,3,4 or 5 and if it is, place the contents of either c3,
c4, c5, c6, or c7.
The contents of the cells c1 thru c7 carry FeetPerSecond calculations that
are formulated differently and are done so also by what distance t2 indicates.

The controlling cell is t2 whether or not it is greater or less than 7.4 as
the formula for the FeetPerSecond change once the distance passes 7.5 or is
less than 7.

All said and done, if it were possible to add 5 more IF statements referring
to DG193 and the 5 possiblities of content, I could do it, but my apologies,
that is about my level of skill and am having trouble getting past that.
Appreciate any input, , ,

"Ron Rosenfeld" wrote:

On Sun, 24 Dec 2006 07:23:00 -0800, Shu of AZ
wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7)))))



There are a number of different ways of solving this problem. They vary in
complexity depending on the complexity you require.

Here's one way, given your parameters:

=IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),
CHOOSE(DG192,D3,D4,D5,D6,D7))

You could also set up lookup tables.
--ron

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default IF Dilema

=IF(AND(T2<7.5,OR(DG193={1,2,3,4,5})),CHOOSE(DG193 ,C3,C4,C5,C6,C7),"OTHER")


"Shu of AZ" wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 695
Default IF Dilema

=IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7,C8,C9,C10,C 11,C12,C13),0)


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 130
Default IF Dilema

I can't thank you enough. I placed another IF statement concerning dg192 and
t2 greater than 7.4 where the "OTHER" result was and its working. Thank you
again

"Teethless mama" wrote:

=IF(AND(T2<7.5,OR(DG193={1,2,3,4,5})),CHOOSE(DG193 ,C3,C4,C5,C6,C7),"OTHER")


"Shu of AZ" wrote:

My dilema is that in the same statement you read below, I have to also ask
also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF
statements. Any ideas on how to reduce this statement?


=IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5), C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7.5 ),C6,IF(AND(DG192=5,T2<7.5),C7)))))



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default IF Dilema

On Sun, 24 Dec 2006 08:37:00 -0800, Shu of AZ
wrote:

Thanks Ron, I tried your formula and received a value error.


That will happen if the first argument of the CHOOSE function is not [1-5].


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sorting dilema Bobbie Excel Discussion (Misc queries) 3 January 5th 05 07:01 PM


All times are GMT +1. The time now is 06:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"