is there a For(<variable>,<low range>,<high range>) like command?

**bweslake** · 10-08-2014, 01:57 AM

I made a schedule planner, a few google searches and some brainstorming later and i couldnt find a solution i could make sense of for looping the huge string of if logic to to infinity, or until out process has an upset.

=IF(A20=$B$2,"SWITCH",IF(AND(A20-$B$2<$B$1/24,A20-$B$2>0),"CHARGING",IF(A20-$B$2=$B$1/24,"Switch",IF(AND(A20-$B$2>$B$1/24,A20-$B$2<($B$1+2)/24),"STEAM TO B/D",IF(AND(A20-$B$2>($B$1+2)/24,A20-$B$2<($B$1+7)/24),"WATER QUENCH",IF(AND(A20-$B$2>($B$1+7)/24,A20-$B$2<($B$1+7.5)/24),"VENTING",IF(AND(A20-$B$2>($B$1+7)/24,A20-$B$2<($B$1+9)/24),"DRAINING",IF(AND(A20-$B$2>=($B$1+9)/24,A20-$B$2<($B$1+11)/24),"CUTTING",IF(AND(A20-$B$2>($B$1+11)/24,A20-$B$2<($B$1+17)/24),"MAINTENANCE WINDOW",IF(AND(A20-$B$2>($B$1+17)/24,A20-$B$2<($B$1+18)/24),"O2 FREE & PRESSURE TEST",IF(AND(A20-$B$2>($B$1+18)/24,A20-$B$2<($B$1+21)),"LATEST VAPOR HEAT",IF(A20-$B$2=42/24,"SWITCH"))))))))))))

thats the wall-o-text formula .. if it makes any difference

i need it to repeat on multiples of $B$1

any help is appreciated!

thanks!

**:) Sixthsense :)** · 10-08-2014, 02:16 AM

Why don't you store these calculations in separate cell (instead of repeating it every time) and use that cell reference in your formula?

A20-$B$2

$B$1/24

**bweslake** · 10-08-2014, 02:47 AM

yea haha i was more focused on having it work than optimizing it for the moment .. the schedule goes from A6 to A54 and each line goes through the whole IF(... as the schedule will change as the cycle is 21 hours and a day is 24 hours.

the ($B$1+X)/24 specifies the time ranges for each task according the 21 hour cycle relative to current time

might be easier to attach the document to show you ... i dont see an attach document button :S uhh heres the outputs of that forumula B1 is 21 and B2 is D-1A feed time

the time is calculated by this formula

=(DATEVALUE(MONTH($M$2) & "/" & DAY($M$2) & "/" & YEAR($M$2))+6/24) where M2 is Now() witht he time portion removed

DRUM FEED TIME 21
D-1A FEED Time 10/6/14 4:00 PM
D-2A FEED Time 10/8/14 3:00 AM
D-3A FEED Time 10/7/14 8:00 PM
33-D-1A 33-D-1B
06:00 CHARGING
06:30 CHARGING
07:00 CHARGING
07:30 CHARGING
08:00 CHARGING
08:30 CHARGING
09:00 CHARGING
09:30 CHARGING
10:00 CHARGING
10:30 CHARGING
11:00 CHARGING
11:30 CHARGING
12:00 CHARGING
12:30 CHARGING
13:00 Switch
13:30 STEAM TO B/D
14:00 STEAM TO B/D
14:30 STEAM TO B/D
15:00 WATER QUENCH
15:30 WATER QUENCH
16:00 WATER QUENCH
16:30 WATER QUENCH
17:00 WATER QUENCH
17:30 WATER QUENCH
18:00 WATER QUENCH
18:30 WATER QUENCH
19:00 WATER QUENCH
19:30 WATER QUENCH
20:00 VENTING
20:30 DRAINING
21:00 DRAINING
21:30 DRAINING
22:00 CUTTING
22:30 CUTTING
23:00 CUTTING
23:30 CUTTING
00:00 MAINTENANCE WINDOW
00:30 MAINTENANCE WINDOW
01:00 MAINTENANCE WINDOW
01:30 MAINTENANCE WINDOW
02:00 MAINTENANCE WINDOW
02:30 MAINTENANCE WINDOW
03:00 MAINTENANCE WINDOW
03:30 MAINTENANCE WINDOW
04:00 MAINTENANCE WINDOW
04:30 MAINTENANCE WINDOW
05:00 MAINTENANCE WINDOW
05:30 MAINTENANCE WINDOW
06:00 O2 FREE & PRESSURE TEST

**:) Sixthsense :)** · 10-08-2014, 02:50 AM

Originally Posted by bweslake

might be easier to attach the document to show you ... i dont see an attach document button

To Attach a sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

**bweslake** · 10-08-2014, 03:15 AM

Example.xlsx

thanks for the quick lesson on how to attach a document.

no sensitive data within.

the times are from 06:00 to 06:00 as that is one work day, or 2 shift cycles. it is organized into .5 hour intervals as eventually this process could line up anywhere.

as per the IF(... each piece of equipmenmt will cycle

21 hours CHARGING
.5 hours SWITCH
1.5 hours STEAM TO B/D
5 hours WATER QUENCH
.5 hours VENTING
1.5 hours DRAINING
2 hours CUTTING
6 hours MAINTENANCE WINDOW
1 hour O2 FREE & PRESSURE TEST
3 hours VAPOR HEATING
.5 hours SWITCH

then repeat

i just want the excel to autofill each day when its opened or be able to select a day based on the input for the first feed time from now until forever if desired.
once i can get the formula to behae the way id like for the 1 piece of equipment i can translate it to the other 5.

thanks again

**samba_ravi** · 10-08-2014, 03:23 AM

Can you provide your desired output manually in the excel file

**bweslake** · 10-08-2014, 03:34 AM

well kind of ... what it is displaying is correct ... its just that once it is outside of 42 hours (A20-$B$2=42/24) past the input time for 33-D-1A feed time it will no longer work as the result of the mathematical formal A(x)-$B$2 will be outside the manually entered range. i only entered one full cycle worth of if commands which means the formula will stop working tomorrow. i need it to continue forever.

is there a For(<variable>,<low range>,<high range>) like command?

LinkBack

Thread Tools

Rate This Thread

Display

is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Re: is there a For(<variable>,<low range>,<high range>) like command?

Thread Information

Users Browsing this Thread

Similar Threads

high and low from data range

[SOLVED] Copy/Paste Values from Variable Range to Corresponding Variable Range

Variable in the Range command

Range variable not working in match command

Syntax for Range command with a variable

Bookmarks

Bookmarks

Posting Permissions