combine 2 formula

[tonym07]

Hello

I am trying to combine 2 formula but have difficulty as i do not understand excel that well, these are the 2 formula below

1st =IF(IFERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0),0)=TEXT(B3,"dd/mm/yyyy"),"off Peak"

2nd =IFERROR(IF((LEFT(TEXT(B3,"ddd"))="S")+ISNUMBER(MATCH(INT(B3),$L$3:$L$10,0)), LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak";"Off peak";"Off Peak";"Off peak"}),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak";"Off peak";"Peak";"Peak";"Peak";"Off peak"})),"")

So from 1st formula then add second formula from lookup on wards which has a space is what i need

this is for peak and off peak times with holiday in a separate worksheet

any help would be appreciated

thank you

 

[steve the fish]

If they both work as you expect then put a comma on the end of the first and add the 2nd to it minus the equals sign and add a close bracket to the end of the whole lot.

 

[tonym07]

If they both work as you expect then put a comma on the end of the first and add the 2nd to it minus the equals sign and add a close bracket to the end of the whole lot.

Hello Steve the fish

For example this is the formula i need

=IF(IFERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0),0)=TEXT(B3,"dd/mm/yyyy"),"off Peak",LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak";"Off peak";"Off Peak";"Off peak"}),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak";"Off peak";"Peak";"Peak";"Peak";"Off peak"})),"")

it say you have entered to many arguments for this function ,

 

[steve the fish]

Do the formulas work as you expect?

 

[Special-K99]

What's this part supposed to be doing?

LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak";"Off peak";"Off Peak"})

It will ALWAYS return "Off Peak" regardless of the value in B3.
You can just return the value of "Off Peak", no need for a LOOKUP() at all.

And why are there two types of return value, "Off peak" and "Off Peak" (Note capital P) ?
Is that a typo? If so just return "Off Peak" as specified above, no nedd for LOOKUP.

 

[steve the fish]

Maybe see if this works:

=IF(ISERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0)),IFERROR(IF((LEFT(TEXT(B3,"ddd"))="S")+ISNUMBER(MATCH(INT(B3),$L$3:$L$10,0)),"Off peak",LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak","Off peak","Peak","Peak","Peak","Off peak"})),""),"Off Peak")

 

[tonym07]

What's this part supposed to be doing?

LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak";"Off peak";"Off Peak"})

It will ALWAYS return "Off Peak" regardless of the value in B3.
You can just return the value of "Off Peak", no need for a LOOKUP() at all.

And why are there two types of return value, "Off peak" and "Off Peak" (Note capital P) ?
Is that a typo? If so just return "Off Peak" as specified above, no nedd for LOOKUP.

Hello Special-K99

The formula is supposed to be universal as in Victoria Australia there is peak and off peak and some do shoulder as well in different times , so what i can do with this formula is change the peak,off peak, and shoulder arrangement and also the times that they are at , just say i went with a different retailer they could be different , so the formula i gave was just peak and off peak only thats why you see it was all off peak and that part was for weekend the other part weekday ,

Yes the capital P yes it is just a typo

Thank you Special-K99

As i am buying from National Electricity market

 

[tonym07]

Maybe see if this works:

=IF(ISERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0)),IFERROR(IF((LEFT(TEXT(B3,"ddd"))="S")+ISNUMBER(MATCH(INT(B3),$L$3:$L$10,0)),"Off peak",LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak","Off peak","Peak","Peak","Peak","Off peak"})),""),"Off Peak")

Hello Steve the Fish


Thank you that worked ,

How do you know which one is for the weekend just say on the weekend shoulder from 10am-7pm how could i modify the formula?

 

[steve the fish]

Judging by what you said before then its this:

=IF(ISERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0)),IFERROR(IF((LEFT(TEXT(B3,"ddd"))="S")+ISNUMBER(MATCH(INT(B3),$L$3:$L$10,0)),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak","Off peak","Off Peak","Off peak"}),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak","Off peak","Peak","Peak","Peak","Off peak"})),""),"Off Peak")

The part in red is produced if the date is a weekend and/or is present within L3:L10.

 

[tonym07]

Judging by what you said before then its this:

=IF(ISERROR(VLOOKUP(TEXT(B3,"dd/mm/yyyy"),Holidays!A:A,1,0)),IFERROR(IF((LEFT(TEXT(B3,"ddd"))="S")+ISNUMBER(MATCH(INT(B3),$L$3:$L$10,0)),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","23:30"},{"Off peak","Off peak","Off Peak","Off peak"}),LOOKUP(TEXT(B3,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak","Off peak","Peak","Peak","Peak","Off peak"})),""),"Off Peak")

The part in red is produced if the date is a weekend and/or is present within L3:L10.

Thank you Steve the fish that worked,

thank you for your help +1

Much appreciated