Repeat select a group of cells

[crystalii]

Hello!

I have a worksheet where I have every hour of the year, starting from hour 1 (Jan 01.00 AM), all the way to hour 8760 which is the last hour of December. These hours are in a row in column A. On column B, I have the outdoors temperature for each hour. So B1 contains the outdoors temperature for the first hour of the year and so on till the last hour of the year. Now I have a formula where I am going to calculate the power needed to heat up a school building. The school building is going to have an inner temperature of 22C between 6 AM to 6 PM everyday, and the rest of the day it will have an inner temperature of 15C. The formula is P = k(innerTemperature-outdoorTemperature) and I have a fixed value of k. I want the value P to be on the column C. So C1 is the power to heat up the school when the outdoor temp is B1.

So if we start with the first 24 hours of January as an example. The power for C1-C6 will have an inner temperature of 15C. Then from C6 to C18 we will have 22C for inner temp. Then from C18 to C24 we will have 15C again. I could use the formula for C1 as =k(15-B1) and drag it down to C6, and then replace 15 with 22 for C6 to 18 and so on. But it will be difficult as you know to repeat this for all the 8760 hours. Do you guys know an excel formula which can do this for me?

Thanks in advance!

 

[crystalii]

OK, problem was brackets

in C2
=LOOKUP(A2-1,{0,6,17},{15,22,15}-B2)*3248
and copy down

It's still not working, same error. Please I have uploaded the file, can you find what the problem is? I would much appreciate it. I have uploaded the document here. http://docdro.id/tMvFDGA

 

[Special-K99]

It works fine - no errors.

I downloaded your file and typed in the quoted formula in your reply above in C2.

It worked.

Nothing else for me to do on this.

Goodbye.