Hello

I am looking for a way to subtract to collums.

In the first A there is always a value.

In the second B a value needs to be added.

When the second value is added I want collum B to substract from A.

Then I want the result of all to be given in a cell, not just B1-B2 but (B1:B50)-(A1:A50).

The thing is that if collum B is empty, I don't want that line to be counted.

Maybe it is a bit complicated.

I started with:

But I have 600 Lines

Can anyone help me

]]>I am looking for a way to subtract to collums.

In the first A there is always a value.

In the second B a value needs to be added.

When the second value is added I want collum B to substract from A.

Then I want the result of all to be given in a cell, not just B1-B2 but (B1:B50)-(A1:A50).

The thing is that if collum B is empty, I don't want that line to be counted.

Maybe it is a bit complicated.

I started with:

Code:

`=SUM(IF(B1<>0;B1-A1;"");IF(B2<>0;B2-A2;""))`

Can anyone help me

I am simply trying to set a cell equal to another. For example in cell A1, i have input the number 1,360.90. In cell A2 I have put =A1. The number it returns is 1,334.87. WHY THE HELL IS THIS HAPPENING?

]]>I have a large set of data with different formulas that I would like to add the following formula to:

*Sheet1!B2

Is there a quick way to do this without changing all the formulas one by one?

Thanks

]]>*Sheet1!B2

Is there a quick way to do this without changing all the formulas one by one?

Thanks

Hi there,

I am trying to have my cell block change color over a series of time based on the time that has been inserted. For example if I enter in the time of 03:21 AM the cell will stay green for 30 minutes, after the 30 minutes it will turn yellow for the next hour and a half until the 2 hour mark is reached at that point it will turn red. Honestly I am not sure if this is possible, but any help would be greatly appreciated.

]]>I am trying to have my cell block change color over a series of time based on the time that has been inserted. For example if I enter in the time of 03:21 AM the cell will stay green for 30 minutes, after the 30 minutes it will turn yellow for the next hour and a half until the 2 hour mark is reached at that point it will turn red. Honestly I am not sure if this is possible, but any help would be greatly appreciated.

I am sure what I want to do is quite simple but I can not figure it out. I am creating a spreadsheet for my companies Bonuses throughout the year. I have a calendar type set up for each month with the dates up top and the employees on the side that adds up all the days that an employee is out. I want to set it up that if there is a zero under the quarterly bonuses column for that employee it will add how much the bonus is automatically. but if there is any number other than zero there nothing will be added

]]>This should be a basic formula but I can't seem to figure it out. I've asked similar questions in the past but my brain is not working at the moment to figure out how to solve this. It's very basic, I would like to have a formula in the highlighted cells (the weight cells next to them would be dynamic and would change, but the formula would return the appropriate value).

If the weight value is 0.8 LB, the formula should return the rate for 1 LB, so it would essentially lookup the table and try to find a rate for 0.8 LB and if it can't find it then it would look for the rate with the next higher weight, in the example attached being 1.0 LB.

Thanks

If the weight value is 0.8 LB, the formula should return the rate for 1 LB, so it would essentially lookup the table and try to find a rate for 0.8 LB and if it can't find it then it would look for the rate with the next higher weight, in the example attached being 1.0 LB.

Thanks

I need a way to calculate the total number of red cells that follow the previous red cells ONLY diagonally. The cells that needs to be calculated

are colored in red.

(if it helps, you can assume reds as 1, and grays 0, but in the example below I colored 1s in white just to show which ones are counted).

Here is a picture of sample data:

data.png

Here is some possible scenarios to clarify it.

possible scenarios.png

I hope I explained it, please ask if it isn't clear.

I tried to find a solution with nested ifs but couldn't figure it out. Can someone help please? thanks.

]]>are colored in red.

(if it helps, you can assume reds as 1, and grays 0, but in the example below I colored 1s in white just to show which ones are counted).

Here is a picture of sample data:

data.png

Here is some possible scenarios to clarify it.

possible scenarios.png

I hope I explained it, please ask if it isn't clear.

I tried to find a solution with nested ifs but couldn't figure it out. Can someone help please? thanks.

Hello,

I hope you all can help. I have a spreadsheet with dozens of sheets that contain pivot tables. It's based on a year's time with two columns for each month. One for Actual and one for Goal. Because of the way the pivot tables are set up, I need to have columns for each, but I only want one showing. The Goal column should be unhidden with the Actual hidden until the current month arrives. Then I would hide the Goal and unhide the actual.

Is there a way I can hide, for example, all of the July Goal Columns on each sheet and unhide all of the July Actual columns at once?

Thank you for any advice/guidance you can give.

Moderator's note: Cross-posted at https://www.mrexcel.com/forum/excel-...-workbook.html

]]>I hope you all can help. I have a spreadsheet with dozens of sheets that contain pivot tables. It's based on a year's time with two columns for each month. One for Actual and one for Goal. Because of the way the pivot tables are set up, I need to have columns for each, but I only want one showing. The Goal column should be unhidden with the Actual hidden until the current month arrives. Then I would hide the Goal and unhide the actual.

Is there a way I can hide, for example, all of the July Goal Columns on each sheet and unhide all of the July Actual columns at once?

Thank you for any advice/guidance you can give.

Moderator's note:

Hi:

I am attempting to solve a problem in the attached excel example file to dynamically turned off a water schedule to Old Houses based on when New Houses are being built. However I only want to turn off the set water schedule if the Old House is ALSO within "X" of less miles from the New House being built.

I have made an attempt at the formula, but I keep getting the wrong result. I have created a "Hard Coded" example of intended outputs (yellow boxes). The formulas in the "yellow boxes" are not meant to be dynamic and do not consider all variables, they simple get the intended answer and I try to show how to get to the answer. My dataset will end up being 300 Old Houses by 300 New Houses, but I have only included 3 Old Houses and 3 New Houses in this simplified example.

Just to recap: I am trying to make a dynamic formula which gives the "New Water Schedule" based on shutting in each "Old House" water line schedule but only during the times of the New House construction AND only if the Old House is within "X" (Cell C2) or less miles away from any New House under construction.

__I have given the following as inputs (blue font cells):__

1. Matrix of each Old House to each New House distance in miles

2. Time-frame of New House Construction ((1=New House is under construction (Shut off Old House waterlines during this timeframe and if less than or equal to "X" (Cell C2) miles from ANY New House under construction), 0=No construction planned during this time (Keep normal Water Schedule))

3. Normal Planned Water Schedule to Old Houses in Gallons (without any shut-ins due to construction)

**My desired Outputs:**

Updated Water Schedule which "shuts-off" when New Houses are under construction AND the Old House is "X" (Cell C2) or Less Miles away from the New House being built

I appreciate the time to look into this for me and let me know if I can clarify anything.

I am attempting to solve a problem in the attached excel example file to dynamically turned off a water schedule to Old Houses based on when New Houses are being built. However I only want to turn off the set water schedule if the Old House is ALSO within "X" of less miles from the New House being built.

I have made an attempt at the formula, but I keep getting the wrong result. I have created a "Hard Coded" example of intended outputs (yellow boxes). The formulas in the "yellow boxes" are not meant to be dynamic and do not consider all variables, they simple get the intended answer and I try to show how to get to the answer. My dataset will end up being 300 Old Houses by 300 New Houses, but I have only included 3 Old Houses and 3 New Houses in this simplified example.

Just to recap: I am trying to make a dynamic formula which gives the "New Water Schedule" based on shutting in each "Old House" water line schedule but only during the times of the New House construction AND only if the Old House is within "X" (Cell C2) or less miles away from any New House under construction.

1. Matrix of each Old House to each New House distance in miles

2. Time-frame of New House Construction ((1=New House is under construction (Shut off Old House waterlines during this timeframe and if less than or equal to "X" (Cell C2) miles from ANY New House under construction), 0=No construction planned during this time (Keep normal Water Schedule))

3. Normal Planned Water Schedule to Old Houses in Gallons (without any shut-ins due to construction)

Updated Water Schedule which "shuts-off" when New Houses are under construction AND the Old House is "X" (Cell C2) or Less Miles away from the New House being built

I appreciate the time to look into this for me and let me know if I can clarify anything.

Good day,

Please see enclosed my Net Present Value calculation. I have made a drop down menu for the different currencies so that the formatting automatically changes into what I would like to be, using the Conditional Formatting function.

It looks really nice on the calculation sheet, but the currency formatting is not carried over to the Graph Axis'. It seems to carry over the cell formatting. I can only guess this is a limitation in excel.

However is there another way, maybe via formula's to change the cell formatting to what it is right now? I think the drop down menu I can solve with the IF function as well, but have no idea about a formatting function.

Thanks!!

Ian

Please see enclosed my Net Present Value calculation. I have made a drop down menu for the different currencies so that the formatting automatically changes into what I would like to be, using the Conditional Formatting function.

It looks really nice on the calculation sheet, but the currency formatting is not carried over to the Graph Axis'. It seems to carry over the cell formatting. I can only guess this is a limitation in excel.

However is there another way, maybe via formula's to change the cell formatting to what it is right now? I think the drop down menu I can solve with the IF function as well, but have no idea about a formatting function.

Thanks!!

Ian

So I'm pretty new at this so I don't know if my formula is incorrect or if its something you just can't do with conditional formatting. In short I'm try to create a formula where if the value of two different cells drops below 1 another cell will turn yellow. The formula I'm using is:

IF(AND(B2=0,OR(C2=0,D2=0)))

Essentially if B2 is 0 and either C2 or D2 is 0 then A2 will turn yellow. so far I'm having no luck with this working. Any help will be greatly appreciative.

]]>IF(AND(B2=0,OR(C2=0,D2=0)))

Essentially if B2 is 0 and either C2 or D2 is 0 then A2 will turn yellow. so far I'm having no luck with this working. Any help will be greatly appreciative.

is it possible to get this spreadsheet updated for 6 animals and 5 cuts?

Edit Mod post related to https://www.excelforum.com/excel-gen...g-contest.html

]]>Edit Mod post related to https://www.excelforum.com/excel-gen...g-contest.html

Hi All,

Have a pivot table which is applying a difference from scenario between two periods (value field settings, show values as, difference from).

Currently using the custom number format:**#,##0,;(#,##0,);-**

Problem is the signage is switched.

Is there an easy way to switch the signage by modifying the custom number format above?

Thanks

]]>Have a pivot table which is applying a difference from scenario between two periods (value field settings, show values as, difference from).

Currently using the custom number format:

Problem is the signage is switched.

Is there an easy way to switch the signage by modifying the custom number format above?

Thanks

I had copied some values from a website, and wanted the sum of them. there are two columns in particular that i require the sum of. One is the time taken and two are the numbers corresponding to the same. WHile I am able to add the time, I am unable to add the numbers. Its very wierd. I cant perform any operations on the numbers, tried googling and changing formatting etc.

Please help (im not particularly new to excel, yet I have never seen anything like this -_-)

Please help (im not particularly new to excel, yet I have never seen anything like this -_-)

Hi All,

Is there an easy way to to highlight YTD (Year to Date) columns in a pivot table based on the current month contained via drop down list in cell A1.

I've attached a simple example showing what the ideal output would be.

Any help or links would be appreciated

Is there an easy way to to highlight YTD (Year to Date) columns in a pivot table based on the current month contained via drop down list in cell A1.

I've attached a simple example showing what the ideal output would be.

Any help or links would be appreciated