If/Then Formula Help

[csutton]

I have a situation that looks like this:
This is a spreadsheet to determine time spent on a project dealing with TV SD & HD channels

Each TV can have 32 channels of SD max
Each HD TV can have 24 channels of HD max

Time spent on this project is divided like this, where 1 = 1 day then
1 SD TV = .33
2 SD TV = .5
3 SD TV = 1
1 SD and 1 HD = .66
2 SD and 1 HD = .75
2 SD and 2 HD = 1
2 HD 1 SD = 1
3 HD 1 SD= 1.25

I have an excel document with the amount of channels each TV needs to have divided into 2 columns ( A= # SD channels, B = HD # of HD channels) I am trying to make a third column that takes the channels in each of these two columns and finds the expected time spent on each TV.

I need help formulating the right if/then formula for column "C" which is the time spent column since all of those combinations have different outcomes.

I hope this makes sense. Please help!!

 

[csutton]

I have a formula that encompasses some of the situations, if that will help anyone get started, or have a better understanding of what I'm looking for:

=IF(C368="Complete", 0, IF(E368>32,0.5,IF((E368+D368)>64,0.66, 0.33)))

where D=HD channels, E= SD Channels and this formula resides in the F column for the time spent

 

[csutton]

In "Excel-Speak" as best as I can do, here's what I need:

A nested IF/THEN that reads: if C1= "Complete",0, IF E1>32 AND <96 THEN=.5, IF E1>96 THEN E1=1-- for all of these D1 must also = 0

IF there is a number other than 0 in D, then all of these conditions apply:

IF E1+D1<56 THEN = .66, IF E1+D1>56 AND E1+D1<88 THEN=.75, IF E1+D1>88 AND<104 THEN= 1, IF E1+D1>104 THEN 1.25


Again, I THINK that's what I need... just need help formatting it within the nested formula. Any clues as to what will help will be greatly appreciated!

 

[rsulliva]

From what I gather you already have col's C-E filled in with criteria data.

In G1 enter

  =IF(D1=0,IF(C1="Complete",0,IF(AND(E1>32,E1<96),0.5,IF(E1>96,1,D4))),D4)

In H1 enter

 =IF(D1<>0,IF(E1+D1<56,0.66,IF(AND(E1+D1>56,E1+D1<88),0.75,IF(AND(E1+D1>88,E1+D1<104),1,IF(E1+D1>104,1.25,"")))),"")

The nested formulas exceed excel limits for one cell. Cell G1 will give you the data you need as it will refer to Cell H1 as needed. (H1 is the helper cell).

Modify as you see fit.