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!

 

[Special-K99]

Your description is incorrect.
"The power for C1-C6 will have an inner temperature of 15C.
Then from C6 to C18 we will have 22C for inner temp."

You just said C6 contains 15C, now youre saying 22C. Which is it? It cant be both.

"Then from C18 to C24 we will have 15C again."

Same goes for C18.

What values are in column A ? Just a sequence from 1 to 8760 ?
No leap year then ? There are 8784 hours in a leap year, you havent considered those.

Time in Excel goes from 00:00 to 23:59 so the hour goes from 0 to 23 not 1 to 24, (Jan 01.00 AM) would be hour 2 not hour 1.

Do this

in A1
=DATEVALUE("01/01/2018 00:00:00")+(ROW()-1)/24
and copy down for 8760 or 8784 rows whatever you decide.

in B1
=LOOKUP(HOUR(A1),{0,6,18},{15,22,15})
and copy down for each row

Youll need the values in the column B formula once youve decided what to do about the overlaps you described at the start of this solution.

 

[crystalii]

We can neglect leap years. I have only been given the hours for a regular year. Also you are right, C1-15 should be 15C. The data in A and B are given in this way:

A B

1 -5
2 -6
3 -5
4 -4
5 -6
6 -8
7 -5
8 -7
9 -9
10 -8

When I attempt to put the formula you gave me to A I get #### in the cell and when I highlight it, it says value error.

Your description is incorrect.
"The power for C1-C6 will have an inner temperature of 15C.
Then from C6 to C18 we will have 22C for inner temp."

You just said C6 contains 15C, now youre saying 22C. Which is it? It cant be both.

"Then from C18 to C24 we will have 15C again."

Same goes for C18.

What values are in column A ? Just a sequence from 1 to 8760 ?
No leap year then ? There are 8784 hours in a leap year, you havent considered those.

Time in Excel goes from 00:00 to 23:59 so the hour goes from 0 to 23 not 1 to 24, (Jan 01.00 AM) would be hour 2 not hour 1.

Do this

in A1
=DATEVALUE("01/01/2018 00:00:00")+(ROW()-1)/24
and copy down for 8760 or 8784 rows whatever you decide.

in B1
=LOOKUP(HOUR(A1),{0,6,18},{15,22,15})
and copy down for each row

Youll need the values in the column B formula once youve decided what to do about the overlaps you described at the start of this solution.

 

[Special-K99]

"Also you are right, C1-15 should be 15C. The data in A and B are given in this way:"

Not sure what you mean by that. Description said 6am to 6pm = 22 C1-C15 is 1st hour to the 15th hour = 00:00-14:00 so the temperature should be 22 at some point during that period.

This is still confusing because (A1=1) is actually the time 00:00 - 00:59 (1 less then the input hour), (A6=6) is actually 05:00 - 05:59, so your input is 6 but you actually mean the hour is 5.

I'll continue based on the original description as I dont understand the above.



I misread the requirement in the original description.

This looks better.

in C1
=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

The LOOKUP() is simply looking at the nth hour, subtracting 1 to conform to proper hours (0-23).
If the result is is 0-5 or 17-23 then return 15 otherwise return 22 for hours 6am to 6pm.
Then subtract B1 from it and multiply by k. Replace k with whatever your constant value is.

NOTE: Subtracting a negative number from a negative value turns the double negative into a plus. So
A10 which contains the 10th hour (09:00-09:59) will result in

Hour 10 subtract 1 = 9
9 is between LOOKUP() values 6 and 18 so returns 22
22 - -8 = 22+8 =30
30 * k

Not sure if this is what you want.

Personally if you want this done properly I would ignore any values in column A since it's just a sequence from 1 to 24 and put this in D1
=DATEVALUE("01/01/2018 00:00:00")+(ROW()-1)/24
so the time conforms to proper time (and Excel) standards (putting 24 is confusing, the 24th hour of the day is from 23:00 - 23:59).
then in C1
=(LOOKUP(D1-1,{0,6,17},{15,22,15})-B1)*k
and copy both formulas down.

Don't forget to replace k with your constant value.

 

[crystalii]

"Also you are right, C1-15 should be 15C. The data in A and B are given in this way:"

Not sure what you mean by that. Description said 6am to 6pm = 22 C1-C15 is 1st hour to the 15th hour = 00:00-14:00 so the temperature should be 22 at some point during that period.

This is still confusing because (A1=1) is actually the time 00:00 - 00:59 (1 less then the input hour), (A6=6) is actually 05:00 - 05:59, so your input is 6 but you actually mean the hour is 5.

I'll continue based on the original description as I dont understand the above.



I misread the requirement in the original description.

This looks better.

in C1
=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

The LOOKUP() is simply looking at the nth hour, subtracting 1 to conform to proper hours (0-23).
If the result is is 0-5 or 17-23 then return 15 otherwise return 22 for hours 6am to 6pm.
Then subtract B1 from it and multiply by k. Replace k with whatever your constant value is.

NOTE: Subtracting a negative number from a negative value turns the double negative into a plus. So
A10 which contains the 10th hour (09:00-09:59) will result in

Hour 10 subtract 1 = 9
9 is between LOOKUP() values 6 and 18 so returns 22
22 - -8 = 22+8 =30
30 * k

Not sure if this is what you want.

Personally if you want this done properly I would ignore any values in column A since it's just a sequence from 1 to 24 and put this in D1
=DATEVALUE("01/01/2018 00:00:00")+(ROW()-1)/24
so the time conforms to proper time (and Excel) standards (putting 24 is confusing, the 24th hour of the day is from 23:00 - 23:59).
then in C1
=(LOOKUP(D1-1,{0,6,17},{15,22,15})-B1)*k
and copy both formulas down.

Don't forget to replace k with your constant value.

I don't know what you meant by D1, did you perhaps mean B1? Anyways, I tried to insert =(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k to C1, and this is what I got. https://imgur.com/a/mFRFx1I


Now I tried to instead put =DATEVALUE("01/01/2018 00:00:00")+(ROW()-1)/24 to B1, and I got a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value]#Value [/URL] ! in B1. I tried to put this equation on another empty field thinking maybe you by D1 meant some random empty cell, but I got [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value]#Value [/URL] ! there too. None of the formulas you gave me worked. Is there any error I am making?

Thanks in advance

 

[Special-K99]

Yes you made an error.
You cannot enter the formula like this.

=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

k needs to be a value or a cell reference.
I didn't tell you to enter the formula like this.
Look at what I wrote and read all of it.

...replace k with your constant value.

You typed in "k", you should have entered the value you want k to be, if k is 3 enter 3, if k is 7 enter 7.



Let's start again.

You say you have data in A and B columns. Column A data starts at 1 and increases by 1. You only supplied data up to 10 but since you say these are hours I assume they go up to 24 then start again at 1.
You also have a constant k that is part of a formula.

"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"

1. You havent said what value k is. So Im going to include it in the formula as k. You will need to change k to whatever value you have decided it shoould be.

2. In C1
=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

Dont just type this in, you need to replace k with whatever value you want it to be.
So if k is 3 you type =(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*3
If k is 7 you type =(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*7

then copy the formula down column C.

 

[crystalii]

Yes you made an error.
You cannot enter the formula like this.

=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

k needs to be a value or a cell reference.
I didn't tell you to enter the formula like this.
Look at what I wrote and read all of it.

...replace k with your constant value.

You typed in "k", you should have entered the value you want k to be, if k is 3 enter 3, if k is 7 enter 7.



Let's start again.

You say you have data in A and B columns. Column A data starts at 1 and increases by 1. You only supplied data up to 10 but since you say these are hours I assume they go up to 24 then start again at 1.
You also have a constant k that is part of a formula.

"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"

1. You havent said what value k is. So Im going to include it in the formula as k. You will need to change k to whatever value you have decided it shoould be.

2. In C1
=(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*k

Dont just type this in, you need to replace k with whatever value you want it to be.
So if k is 3 you type =(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*3
If k is 7 you type =(LOOKUP(A1-1,{0,6,17},{15,22,15})-B1)*7

then copy the formula down column C.

Yes I know that I am going to replace k with my own value, and I already did that. I only took the picture to show the error. Here is a new picture with the same error. https://imgur.com/a/UuauPA2

 

[Special-K99]

There's nothing wrong with that formula.
I just typed it into a blank sheet and it accepted it.

However, from your original description

"So C1 is the power to heat up the school when the outdoor temp is B1."

No.
The outdoor temperature is not B1, it has magically changed to "Ostersund".
You've changed the cell references from your original description.
So you need to change the formua.

In C2
=(LOOKUP(A2-1,{0,6,17},{15,22,15})-B2)*k

replace k with your constant.
copy it down the column

 

[crystalii]

Yeah I tried doing that too before but it didn't work. It's really driving me crazy man. https://imgur.com/a/bH5lKmx

 

[Special-K99]

OK, problem was brackets

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