If/Then Formula Help

csutton

New Member
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.

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

csutton

New Member
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

New Member
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

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

In G1 enter
Code:
``  =IF(D1=0,IF(C1="Complete",0,IF(AND(E1>32,E1<96),0.5,IF(E1>96,1,D4))),D4)``

In H1 enter
Code:
`` =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.

Last edited:

Replies
5
Views
129
Replies
0
Views
199
Replies
0
Views
65
Replies
5
Views
142
Replies
92
Views
928

1,190,583
Messages
5,981,787
Members
439,735
Latest member
Tony_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back