Need Help with IF - FIND formula

[Koksonot]

Hello,

I will try to explain you how I want my formula to be in English language

IF cell B101 containts "Tuesday" then (B103-B102)+(B105-B104)+(B107-B106)...

My original idea is something like this, but I don't think it's possible, so the above is enough help for me.

=(b30-b29)+(b32-b31)+(b34-b33) but if B101 contains "Tuesday", continue with +(B103-B102)+(B105-B104)+(B107-B106)...


Thanks in advance,
Nikola

 

[AliGW]

Not completely sure I follow. What does the ellipsis (...) indicate?

 

[brian.wethington]

Generally the ellipses indicates a continuation and then you show what the ending of the scenario would be. Since I don't know what the ending of the scenario is, or what you are trying to accomplish exactly I can show you what I think you are trying to do.

For the first part you could use a fairly simple array formula, you will enter the formula and instead of just pressing enter you will need to press Ctrl+Shift+Enter:

=SUM(IF(MOD(ROW(B29:B34),2)>0,-B29:B34,B29:B34))

When you select the cell but don't go into the cell it will be in curly brackets looking like this:

{=SUM(IF(MOD(ROW(B29:B34),2)>0,-B29:B34,B29:B34))}

For the 2nd part (again entered as an array, using Ctrl+Shift+Enter to confirm:

=SUM(IF(MOD(ROW(B29:B34),2)>0,-B29:B34,B29:B34))+IF(B101="Tuesday",SUM(IF(MOD(ROW(B102:B107),2)=0,-B102:B107,B102:B107)),0)

Again, I am not sure where your ranges are beginning and ending. It just looks to me like you are trying to alternate sums in your ranges positively and negatively based on rows.

 

[AliGW]

Generally the ellipses indicates a continuation and then you show what the ending of the scenario would be.

That's my point.

 

[Koksonot]

Either I don't get you, or you don't get me... the (...) is nothing related to the formula, I am used to finish sentences with ...

Let me explain better. I am preparing a schedule for my co-workers. So the time they spend working is written like this

b20 from 10:30 AM>One
b21 to 10:45 AM > Task
---
b22 from 11:20 PM > Another
b23 to 11:40 PM> Task
--
b24
b25.

(FROM and TO are not included in the cells, so the cells are with numbers only don't worry)

So when I sum up the whole working time, my formula is =(b21-b20)+(b23-b22)+(b25-b24) etc etc.... (lets say this is B100)

But I have extra cells, which are indicating a BONUS table with cells if someone fills all of theirs ordinary cells.

So I want to add this: If the one of the bonus cells (let say b200) says Tuesday, than also sum up other cells in the BONUS TABLE (example. If B200 is Tuesday, than I want the cell B300 to have this formula =(b202-b201)+(b204-b203)+(b206-b205)+(b208-b207) etc etc)

I imagine the final image like this.
If b200 = tuesday, than the formula above would be calculated in B300 CELL.
If B200 = Wednesday, than the formula above would be calculated in (let's say) B301 CELL.
IF b200 = thursday than B302 CELL..

Got it?

Thanks for the help.

 

[brian.wethington]

Unsure if you looked at my solutions; perhaps you could expand the ranges for your needs. If you would like to create a different cell that contains the 2nd portion then it might look something like this (again an array so confirmed with ctrl+shift+enter):

=IF(Len(B101)>0,SUM(IF(MOD(ROW(B102:B107),2)=0,-B102:B107,B102:B107)),"")

This will show a blank if there is no entry in B101, if there is any entry in it then it will produce the range sum. If you are wanting to be less flexible and require it to be Tuesday in B101 than you could leave it like it was prior (again confirm as an array):

=IF(B101="=Tuesday",SUM(IF(MOD(ROW(B102:B107),2)=0,-B102:B107,B102:B107)),"")

Expand ranges as you need.

 

[Koksonot]

Guess I don't get it quite good. Why do I need SUM(IF(MOD(ROW(B102:B107),2)=0,-B102:B107,B102:B107)),"") ??? What does this does?

in cells that are in b102-b107 range, I want this math to be done:

=(b103-b102)+(b105-b014)+(b107-b106) etc...

I don't get what MOD(ROW does.
When I paste this formula (and it's totally synchronized with my cell numbers) I recieve #VALUE!.

I filled all cells that are in the range of MOD(ROW and still nothing.

I am newbie with excel, so I don't mind easier but longer formula if available.

Can't it be IF(B101="Tuesday",SUM((b103-b102)+(b105-b014)+(b107-b106))

Something like this, huh?

Thanks Brian!

 

[Koksonot]

I did it Brian, thanks for the help it meant a lot.

I only googled how to convert result in time and here is the final formula

=IF(B101="Tuesday",TEXT((B103-B102)+(B105-B104)+(B107-B106),"h:mm"))

Probably I've should mention it's time

 

[brian.wethington]

First, what I provided is an array formula and as I have mentioned it must be confirmed by pressing ctrl+shift+enter rather than just enter. So you copy my formula, paste the formula into the desired cell, and instead of just pressing Enter to confirm the formula you press Ctrl+Shift+Enter

If it really is simpler for you to use the formula you have, then yes you can do something like you are stating, but know if you change your ranges or anything it will be more complicated to update. If your ranges are anticipated to stay as small as you are stating right now, then this would not be too large of an issue, but you keep using etc, and ... I know you have stated that the ellipses have no bearing on the formula, but I get the feeling there is more to this than you are stating here. At any rate if it really is easier for you, then yes you can do something like:

=If(B101 = "Tuesday",Sum(b103,-b102,b105,-b104,b107,-b107),"")

I am just now going to explain to you what my formula did

=IF(B101="Tuesday",SUM(IF(MOD(ROW(B102:B107),2)=0,-B102:B107,B102:B107)),"")

it first checks to see if B101 is Tuesday, if it is then you go to the sum section of the forumula: It looks at the entire range and determines if the row that it is looking at is odd or even, if it is even then it uses the negative value of the cell, if it is odd it uses the positive value of the cell thus transferring the positive or negative values automatically in whatever range you decide to use, you don't have to go through the work of determining it. Again this is only truly necessary if you could change the range, or the range becomes pretty large. It is much easier to write:

=IF(B101="Tuesday",SUM(IF(MOD(ROW(B102:B121),2)=0,-B102:B121,B102:B121)),"")

than to try to figure that out

=If(B101 = "Tuesday",Sum(B103,-B102,B105,-B104,B107,-B106,B109,-B108,... , B121,-B120)

As far as your formula, I don't believe you should need to use the text option unless there is something strange about your time entry (which certainly can occur). However in most cases with time you can add and subtract normally and the format the cell to [h]:mm:ss. Just an aside.