Hi All,

Ive spent two years developing a CRM system and now the last thing i need to do is the dating system. sales and performance etc are all filtered by year and week.

The calendar used by my place of work is a 4-4-5 calendar, so 4 weeks, 4 weeks, then 5 weeks per quarter.

The year starts on the first week, beginning with a Sunday in February. So last years week 1 was 31st Jan 2016. This years week 1 is 29th January.

I only need this to work for the next 4 years, So I can work out the date values in Excel for each of the years, and make excel translate dates that fall into those values into a year, but how can I do the same for weeks?

There are a few threads that already attack this problem, but nothing seems to solve it adequately.

thank you in advance

]]>Ive spent two years developing a CRM system and now the last thing i need to do is the dating system. sales and performance etc are all filtered by year and week.

The calendar used by my place of work is a 4-4-5 calendar, so 4 weeks, 4 weeks, then 5 weeks per quarter.

The year starts on the first week, beginning with a Sunday in February. So last years week 1 was 31st Jan 2016. This years week 1 is 29th January.

I only need this to work for the next 4 years, So I can work out the date values in Excel for each of the years, and make excel translate dates that fall into those values into a year, but how can I do the same for weeks?

There are a few threads that already attack this problem, but nothing seems to solve it adequately.

thank you in advance

Hi I am making an excel sheet to our staff canteen each item is listed in different sheets.

I want to know how can I get the total from cell E to cell H of a specific person. Staff had taken stuff different days with different quantities. Please tell me a formula to get the sum of a specific person in cell H.

Your help will be very much appreciated. Thank you in advance.

I want to know how can I get the total from cell E to cell H of a specific person. Staff had taken stuff different days with different quantities. Please tell me a formula to get the sum of a specific person in cell H.

Your help will be very much appreciated. Thank you in advance.

hi all...

how to find balance with duplicate name & based on name and date entry...

please, check this file.

i have use

bt not working

any assistance, much appreciated...

john m

how to find balance with duplicate name & based on name and date entry...

please, check this file.

i have use

Code:

`=IF(A2<>"",C2,D1-C2)`

any assistance, much appreciated...

john m

I have a data that has 4 columns.The rows after some point will have a value of zero.

I need to get the value of the previous row of column which is greater than zero.

For example; here after last row, all the value become zero.

I then need the value in row 18 to appear in first cell in a row that is greater than zero.

A B C D

1- 122.20 0.00 133.90 0.00

2- 122.95 0.00 134.72 0.00

3- 0.00 0.00 0.00 0.00

4- 128.59 0.00 0.00 0.00

5- 131.60 0.00 0.00 0.00

6- 0.00 139.50 0.00 127.65

7- 0.00 138.86 0.00 127.07

8- 0.00 0.00 0.00 133.01

9- 0.00 0.00 0.00 0.00

10-0.00 0.00 0.00 0.00

11-0.00 0.00 0.00 0.00

12-145.50 0.00 0.00 137.52

13-0.00 0.00 133.48 0.00

14-0.00 0.00 134.89 0.00

15-0.00 0.00 0.00 0.00

16-0.00 0.00 0.00 0.00

18-145.50 138.86 134.89 137.52

test file attached herewith

thanks in advance

I need to get the value of the previous row of column which is greater than zero.

For example; here after last row, all the value become zero.

I then need the value in row 18 to appear in first cell in a row that is greater than zero.

A B C D

1- 122.20 0.00 133.90 0.00

2- 122.95 0.00 134.72 0.00

3- 0.00 0.00 0.00 0.00

4- 128.59 0.00 0.00 0.00

5- 131.60 0.00 0.00 0.00

6- 0.00 139.50 0.00 127.65

7- 0.00 138.86 0.00 127.07

8- 0.00 0.00 0.00 133.01

9- 0.00 0.00 0.00 0.00

10-0.00 0.00 0.00 0.00

11-0.00 0.00 0.00 0.00

12-145.50 0.00 0.00 137.52

13-0.00 0.00 133.48 0.00

14-0.00 0.00 134.89 0.00

15-0.00 0.00 0.00 0.00

16-0.00 0.00 0.00 0.00

18-145.50 138.86 134.89 137.52

test file attached herewith

thanks in advance

Hi Friends,

Would anyone help me out to put all combined data in one cell breaking into several line?

For example: Say cell A5 & B6 data needs to combine in cell C6, whereas in the cell C6 data should be shown one(B6) under other (A5).

Note : Fomula "=(A5&B6)" gives result in single line.

Thanks in advance

]]>Would anyone help me out to put all combined data in one cell breaking into several line?

For example: Say cell A5 & B6 data needs to combine in cell C6, whereas in the cell C6 data should be shown one(B6) under other (A5).

Note : Fomula "=(A5&B6)" gives result in single line.

Thanks in advance

Hello,

I would like to automatically create categories for certain transactions off my credit card statement in excel. I tried putting in a if statement, but that was only for one cell. I would like to apply multiple if statements (or something similar) to a whole column how would i got about doing this?

For instance i started out with a if statement that follows...

=If(b2="Albert's Mexican Food", Restaurant, "")

I would then add multiple if statements such as this, and automatically return the category to the specified column.

]]>I would like to automatically create categories for certain transactions off my credit card statement in excel. I tried putting in a if statement, but that was only for one cell. I would like to apply multiple if statements (or something similar) to a whole column how would i got about doing this?

For instance i started out with a if statement that follows...

=If(b2="Albert's Mexican Food", Restaurant, "")

I would then add multiple if statements such as this, and automatically return the category to the specified column.

Hello,

I would like to automatically create categories for certain transactions off my credit card statement in excel. I tried putting in a if statement, but that was only for one cell. I would like to apply multiple if statements (or something similar) to a whole column how would i got about doing this?

For instance i started out with a if statement that follows...

=If(b2="Albert's Mexican Food", Restaurant, "")

I would then add multiple if statements such as this, and automatically return the category to the specified column.

]]>I would like to automatically create categories for certain transactions off my credit card statement in excel. I tried putting in a if statement, but that was only for one cell. I would like to apply multiple if statements (or something similar) to a whole column how would i got about doing this?

For instance i started out with a if statement that follows...

=If(b2="Albert's Mexican Food", Restaurant, "")

I would then add multiple if statements such as this, and automatically return the category to the specified column.

I’m looking to ‘reverse’ calculate an interest rate. Does anyone know of a formula to find an unknown rate on a loan? The principal, monthly payment amount and number of months are known, but the rate is not.

Any help would be appreciated. Thanks.

]]>Any help would be appreciated. Thanks.

I need to add how many times two variables happen in a row. I have excel 2010 and I am wanting it to draw from a different sheet.

Example

A B C

row2 dog cat rat

row3 dog rat boy

row4 rat cat girl

row5 dog cat man

how many times does dog and cat show up in the same row. Answer should be two. I tried this formula but it keeps giving me errors.

=COUNTIF(Sheet1!A2:A1000,"dog", Sheet1!B2:C1000, "cat")

and

=COUNTIFs(Sheet1!A2:A1000,"dog", Sheet1!B2:C1000, "cat")

Thanks

Nick

]]>Example

A B C

row2 dog cat rat

row3 dog rat boy

row4 rat cat girl

row5 dog cat man

how many times does dog and cat show up in the same row. Answer should be two. I tried this formula but it keeps giving me errors.

=COUNTIF(Sheet1!A2:A1000,"dog", Sheet1!B2:C1000, "cat")

and

=COUNTIFs(Sheet1!A2:A1000,"dog", Sheet1!B2:C1000, "cat")

Thanks

Nick

Hi,

I just joined this site. Sorry if this thread is in the wrong place or if I'm doing something else wrong (this is my first forum site).

Here's my question - So if I have two excel files, one is called “sales” and the other is called “quarter 4 2016”. In the “sales” file there’s a cell with a formula that equals cell A1 in the “quarter 4 2016” file. If I move cell A1 (and it's contents) in “quarter 4 2016” A2 (still in quarter 4 2016), I want the cell in "sales" that before equaled A1 in "quarter 4 2016" to update and change to now equal A2 in "quarter 4 2016".

I know how to do this between different sheets in the same workbook, but not between different workbooks.

Thanks!

]]>I just joined this site. Sorry if this thread is in the wrong place or if I'm doing something else wrong (this is my first forum site).

Here's my question - So if I have two excel files, one is called “sales” and the other is called “quarter 4 2016”. In the “sales” file there’s a cell with a formula that equals cell A1 in the “quarter 4 2016” file. If I move cell A1 (and it's contents) in “quarter 4 2016” A2 (still in quarter 4 2016), I want the cell in "sales" that before equaled A1 in "quarter 4 2016" to update and change to now equal A2 in "quarter 4 2016".

I know how to do this between different sheets in the same workbook, but not between different workbooks.

Thanks!

Hi everybody,

Could you please help me with the following task in a more fashionable way, than I did it in the attached file?

My task is to calculate a**monthly ROS** (rate of sale - basic average, I used countif to avoid 0 values) in a fiscal year for stores that have changed their concepts, but the calculations should be based on these conditions:

**Simply said I would like to see the ROS before the change**, which is in the column B **(day.month.year format)**. But here are the tricks:

1.**Stores F and G** are ideal. They changed before the 1.4.2015 so I would like to see the ROS calculated for the whole period as I do not have the data before their change.

2.**Store A** is still easy as I would like to see its ROS from the period before the change: from 1.4.2015 to 1.1.2016. **Stores B and C** work on the same principle. (C is a little bit tricky but I will return to it at the end).

3.**Store D** is simple as well. I do not have data before the change so for the ROS in my fiscal year I want to see all the data I have.

4. Finally**store E**, where only 2 months data before the change are available, which I do not consider as representative to call it a ROS for this fiscal year and therefore my ROS takes the data from the date of the change.

*Summary:*

**I would like to see a solution which goes with this logic:**

If the change was before the 1.4.2015 take all data.

If the change happened after the 1.4.2015 but before the 1.9.2015 (so that I have at least 5 month data for ROS) take the data after the change and do not look at the data before (like store C).

If the change happened after the 1.9.2015 (included) take all the data before the change and do not take the data after the change

(like store A).

I would be very thankful to you if you could help me formulate this or find another approach how to look at it.

Thank you very much in advance.

Filip

Could you please help me with the following task in a more fashionable way, than I did it in the attached file?

My task is to calculate a

1.

2.

3.

4. Finally

If the change was before the 1.4.2015 take all data.

If the change happened after the 1.4.2015 but before the 1.9.2015 (so that I have at least 5 month data for ROS) take the data after the change and do not look at the data before (like store C).

If the change happened after the 1.9.2015 (included) take all the data before the change and do not take the data after the change

(like store A).

I would be very thankful to you if you could help me formulate this or find another approach how to look at it.

Thank you very much in advance.

Filip

Okay, have a quick question, I'm trying to use the date function to fill out an amortization table...BUT every time I enter in this
I get #Value errors, and I'm not sure why, the cell above is a relative reference to another cell in the table, just wondering how to fix this issue. Any help would be appreciated!

Thanks,

Xandler

]]>
Formula:

=DATE(YEAR(B11),MONTH(B11)+1,DAY(B11))

Thanks,

Xandler

Good evening I need your help.

I can not find a way to format the entire column holidays.

In the attached file in column H on 06/01/2017 day is a holiday

I would like to paint entire column as the other in red, but color me only the first cell

Where am I wrong?

In sheet1 is the list of public holidays

Thank you

I can not find a way to format the entire column holidays.

In the attached file in column H on 06/01/2017 day is a holiday

I would like to paint entire column as the other in red, but color me only the first cell

Where am I wrong?

In sheet1 is the list of public holidays

Thank you

Hi all,

I have been stuck figuring out how to populate cells with values AFTER or BEFORE a given cell(s) with values.

I tried the COLUMN/ROW functions but need to find a better way to do it to incorporate it with a long formula:

Here is an example of what I am trying to do: Find attached :)

I have been stuck figuring out how to populate cells with values AFTER or BEFORE a given cell(s) with values.

I tried the COLUMN/ROW functions but need to find a better way to do it to incorporate it with a long formula:

Here is an example of what I am trying to do: Find attached :)

Hello. I have searched everywhere and tried everything I know but cant seem to get this.

I have daily data over many years. I would like to average the all of one weekday in the same month. For example, All Mondays in November over the course of many years.

It is laid out like this:

...**A**............**B.............C**...................... **BR**..............**BS**

**1** DATE.......11/02/09...11/03/09.............02/14/16.....02/15/16

**2 ** VALUE......2.97.........3.45...................4.9.............1.9

So using the date row I would like to average the Value row for specific days in specific months.

Thanks so much!!!!!!!!!!!!

]]>I have daily data over many years. I would like to average the all of one weekday in the same month. For example, All Mondays in November over the course of many years.

It is laid out like this:

...

So using the date row I would like to average the Value row for specific days in specific months.

Thanks so much!!!!!!!!!!!!