Calculating the breakeven point when we start making money from our initial investment.

In the attachment, I have calculated the Payback Period.

So it works out the full year Cumulative Cash Flow is negative and also partial year using the formuale specified.

Year 0 is the initial investment, but say the initiative is starting in 1 May 2019, is there a way to alter the fraction formulae to be (31/12/19 – Initiative Date) and thus factor 8 months and provide a fraction number earlier?

The logic behind this is it will improve the Payback to be slightly earlier because it is not for full 12 months?

Can this be done Excel community?

Regards

In the attachment, I have calculated the Payback Period.

So it works out the full year Cumulative Cash Flow is negative and also partial year using the formuale specified.

Year 0 is the initial investment, but say the initiative is starting in 1 May 2019, is there a way to alter the fraction formulae to be (31/12/19 – Initiative Date) and thus factor 8 months and provide a fraction number earlier?

The logic behind this is it will improve the Payback to be slightly earlier because it is not for full 12 months?

Can this be done Excel community?

Regards

Hi

Can anyone tell me how I can give something from a drop down list a certain value.

Like associating Apple with the value 1

Banana with the value 2.

So that when you chose "Banana" from the drop down list, a cell returns the value 2.

Can anyone tell me how I can give something from a drop down list a certain value.

Like associating Apple with the value 1

Banana with the value 2.

So that when you chose "Banana" from the drop down list, a cell returns the value 2.

Hi

I am trying to create a conditional formatting rule and if breached it will write a comment.

Please refer to the attachment and if anyone can advise kindly.

Thanks

I am trying to create a conditional formatting rule and if breached it will write a comment.

Please refer to the attachment and if anyone can advise kindly.

Thanks

Dear all,

I have about 1000 columns with different length in the excel file; please kindly find the attached short example. I would like to compare the first column with the rest of columns and find identical items between the first column and column 2, between the first column and column 3, and so on; finally, print the identical items at the end of each column and the last row in the excel file as I show in the example file in red color.

Please kindly note that, in this file, the header name of columns, except for the first column is identical with the contents within the first column; this identical name between each column and its header name should be ignored when searching for identical items. For example, in the attached example, there are 7 columns; I would like to compare the column 1 with each of 6 columns. As you see, the header name of these 6 columns (A, B, C, D, E, F) is identical with the names in the column 1. So when, column 1 compare with column 2, A should be ignored (because it is identical to the header name of column 2) and B and F should be considered as the identical item between column 1 and column 2, and printed (as B,F) , and so on for other columns.

Your help would be highly appreciated in advance.

All the best

I have about 1000 columns with different length in the excel file; please kindly find the attached short example. I would like to compare the first column with the rest of columns and find identical items between the first column and column 2, between the first column and column 3, and so on; finally, print the identical items at the end of each column and the last row in the excel file as I show in the example file in red color.

Please kindly note that, in this file, the header name of columns, except for the first column is identical with the contents within the first column; this identical name between each column and its header name should be ignored when searching for identical items. For example, in the attached example, there are 7 columns; I would like to compare the column 1 with each of 6 columns. As you see, the header name of these 6 columns (A, B, C, D, E, F) is identical with the names in the column 1. So when, column 1 compare with column 2, A should be ignored (because it is identical to the header name of column 2) and B and F should be considered as the identical item between column 1 and column 2, and printed (as B,F) , and so on for other columns.

Your help would be highly appreciated in advance.

All the best

I am working on a annual salary increment file and I am having trouble with implementing an IF function at the beginning of the formula.

This overall formula should do three things:

1. Check if "Increase Recommended" is blank or 0. If true, then just return their original salary (without rounding it)

2. Prorate any value in "Increase Recommended" based on their start date and add it to their existing salary to form their new base salary

3. If receiving any increase, round their revised salary up to the closest hundred dollar

I believe I have the formula working correctly for the proration and rounding but I am struggling to for it to recognize if the "Increase Recommended" column is blank or 0. In this case it is rounding the value which is not correct.

Please feel free to improve my existing formula for proration and rounding as well, I greatly appreciate learning more efficient functions. Thanks!

This overall formula should do three things:

1. Check if "Increase Recommended" is blank or 0. If true, then just return their original salary (without rounding it)

2. Prorate any value in "Increase Recommended" based on their start date and add it to their existing salary to form their new base salary

3. If receiving any increase, round their revised salary up to the closest hundred dollar

I believe I have the formula working correctly for the proration and rounding but I am struggling to for it to recognize if the "Increase Recommended" column is blank or 0. In this case it is rounding the value which is not correct.

Please feel free to improve my existing formula for proration and rounding as well, I greatly appreciate learning more efficient functions. Thanks!

In my worksheet I have the following:

A2 is a date e.g. 01Jan20 to 31Dec20

B2 is the day of the week based on A2 e.g. Mon or Wed or Sun.*Formula in this cell is =TEXT(WEEKDAY(A2), "ddd")*

C3 is the occurrence of that day in any given month, based on A2 e.g. 1 or 2 or 3 or 4 or 5 ie the first wednesday of a month or second wednesday of a month or fifth friday of a month etc.* Formula in this cell is: =INT((DAY(A2)-1)/7)+1*

Examples of data are:

01Jan20 Wed 1

08Jan20 Wed 2

31Jan20 Fri 5

01Feb20 Sat 1

08Feb20 Sat 2

31Dec20 Thu 5

I have been able to identify which days of month, in a new column - D3, are "st", "nd","rd","th" with the following formula*=LOOKUP(DAY(A2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"}*)

01Jan20 Wed 1 st

08Jan20 Wed 2 nd

31Jan20 Fri 5 th

01Feb20 Sat 1 st

08Feb20 Sat 2 nd

31Dec20 Thu 5 th

But this hasn't helped as the formula identifies which days of week are; "st", "nd","rd","th" and not the "occurrence of that day" in any given month. (if that makes sense)

The result I am looking for is as follows;

01Jan20 Wed 1 1st

08Jan20 Wed 2 2nd

31Jan20 Fri 5 5th

01Feb20 Sat 1 1st

08Feb20 Sat 2 2nd

31Dec20 Thu 5 5th

I trust the above makes some sense and thank you for your time and consideration.

]]>A2 is a date e.g. 01Jan20 to 31Dec20

B2 is the day of the week based on A2 e.g. Mon or Wed or Sun.

C3 is the occurrence of that day in any given month, based on A2 e.g. 1 or 2 or 3 or 4 or 5 ie the first wednesday of a month or second wednesday of a month or fifth friday of a month etc.

Examples of data are:

01Jan20 Wed 1

08Jan20 Wed 2

31Jan20 Fri 5

01Feb20 Sat 1

08Feb20 Sat 2

31Dec20 Thu 5

I have been able to identify which days of month, in a new column - D3, are "st", "nd","rd","th" with the following formula

01Jan20 Wed 1 st

08Jan20 Wed 2 nd

31Jan20 Fri 5 th

01Feb20 Sat 1 st

08Feb20 Sat 2 nd

31Dec20 Thu 5 th

But this hasn't helped as the formula identifies which days of week are; "st", "nd","rd","th" and not the "occurrence of that day" in any given month. (if that makes sense)

The result I am looking for is as follows;

01Jan20 Wed 1 1st

08Jan20 Wed 2 2nd

31Jan20 Fri 5 5th

01Feb20 Sat 1 1st

08Feb20 Sat 2 2nd

31Dec20 Thu 5 5th

I trust the above makes some sense and thank you for your time and consideration.

Is there a way to use a message box to prompt a user to edit the contents of a cell if a value is already there?

The cell I am referring to (D1) has long text string values in it occasionally so instead of a message box asking for a new value each time to be typed in it would be helpful for the message box to contain what is already in the cell giving the user the opportunity to edit that value.

]]>The cell I am referring to (D1) has long text string values in it occasionally so instead of a message box asking for a new value each time to be typed in it would be helpful for the message box to contain what is already in the cell giving the user the opportunity to edit that value.

Hi,

I have a cell with 33a21 / 52:334. Is there a formula to flip the contents of the cell using the "/" as a delimiter? So the output would be 52:334 / 33a21?

Thanks.

]]>I have a cell with 33a21 / 52:334. Is there a formula to flip the contents of the cell using the "/" as a delimiter? So the output would be 52:334 / 33a21?

Thanks.

Hello!

Thank you in advance for all the support, I'm wondering if you could help me with an approach to this:

10003|TRUE|[{*day*:*mon*,*amount*:3463.98},{*nig*:*tue*,*amount*:77.09}]

I have a column of data that looks like this, all of the above is in the same cell, I need to extract the numbers, i.e. 3463.98 in one cell and 77.09 in a different cell.

Does anyone have any idea of which formula I can use? I've tried with a combination left, mid, find, etc but I haven't found any success.

]]>Thank you in advance for all the support, I'm wondering if you could help me with an approach to this:

10003|TRUE|[{*day*:*mon*,*amount*:3463.98},{*nig*:*tue*,*amount*:77.09}]

I have a column of data that looks like this, all of the above is in the same cell, I need to extract the numbers, i.e. 3463.98 in one cell and 77.09 in a different cell.

Does anyone have any idea of which formula I can use? I've tried with a combination left, mid, find, etc but I haven't found any success.

I maintain a spreadsheet which contains formulas summing certain cells etc and linking between workbooks. Data is added to the spreadsheet which is then sorted to the correct place in the list. I want the formulas to carry on adding the same data as it did before the new data was added. The problem I have is that the absolute references do not adjust themselves to take account of new data added this way (as it would if rows were inserted). Example attached.

I have tried using name ranges, which has the same result, using the workbook reference but nothing seems to work. If there anyway to force the system to keep the references.

Thanks for your help.

I have tried using name ranges, which has the same result, using the workbook reference but nothing seems to work. If there anyway to force the system to keep the references.

Thanks for your help.

Hi,

Was wondering if someone could help with the excel camera tool. Since going to Windows 10, the camera tool does not show large portions of the original data.

I have attached a file as an example. Wondering if there is some setting that needs to be checked or un-checked. Thanks.

Was wondering if someone could help with the excel camera tool. Since going to Windows 10, the camera tool does not show large portions of the original data.

I have attached a file as an example. Wondering if there is some setting that needs to be checked or un-checked. Thanks.

Hi I am sort of a newbie when it comes to Excel formulas. Easily confused with which formula to use when....

So Im really hoping to get some help here :) Thank you so much in advance.

Here is the excel problem:

I want to compare the names columns if there are matches. Afterwards, compare if the corresponding ages are same. If both are satisfied, then it returns "Y".

Otherwise, if there are no name matches, or if names match but ages don't, then it returns "N". How do I do this? I have attached screenshot. (Cant seem to attach the file itself. :( Thank you so much!

excel problem.png

]]>So Im really hoping to get some help here :) Thank you so much in advance.

Here is the excel problem:

I want to compare the names columns if there are matches. Afterwards, compare if the corresponding ages are same. If both are satisfied, then it returns "Y".

Otherwise, if there are no name matches, or if names match but ages don't, then it returns "N". How do I do this? I have attached screenshot. (Cant seem to attach the file itself. :( Thank you so much!

excel problem.png

Hello all,

This might be a silly one. A group of college friends play the NBA 2K video game competitively in our spare time. We were considering different lineups, and I being the Data Analyst for my career, thought I could build something out in Excel but find myself stumped. We have six players on the team. Only five can play at one time. Of the six players, four have two player prototypes. This leads to ten total prototypes. What I am looking to do is create every possible five man lineup possible, with one rule; no one user can be in the same lineup twice. Making this slightly more difficult for me than saying "Every possible combination". I'm looking to make every possible unique set of 5, and no name can be placed twice in the same set.

2k Builds.PNG

For those who follow basketball, in this case I would not be concerned with mismatched positions. Just looking for a way to sweep up and display every possible 5 build set, while restricting to the name listed just once in each set.

Thanks in advance for any assistance!

EDIT : I have attached the sample file now as well with a view of what I'm looking to spit out automatically. The "All Lineups" tab contains what you see in the screenshot and a sample of where I am looking to get. In this instance. I have removed "Evan" from the 5 person set. This leaves Fent, Beeks, Hollis, Derek, Mendez. I have then placed each of the 8 possible combinations that can come with THAT specific set of 5 based on the potential variable of certain names have two builds. I would be looking to replicate that process for each 5 person scenario.

This might be a silly one. A group of college friends play the NBA 2K video game competitively in our spare time. We were considering different lineups, and I being the Data Analyst for my career, thought I could build something out in Excel but find myself stumped. We have six players on the team. Only five can play at one time. Of the six players, four have two player prototypes. This leads to ten total prototypes. What I am looking to do is create every possible five man lineup possible, with one rule; no one user can be in the same lineup twice. Making this slightly more difficult for me than saying "Every possible combination". I'm looking to make every possible unique set of 5, and no name can be placed twice in the same set.

2k Builds.PNG

For those who follow basketball, in this case I would not be concerned with mismatched positions. Just looking for a way to sweep up and display every possible 5 build set, while restricting to the name listed just once in each set.

Thanks in advance for any assistance!

EDIT : I have attached the sample file now as well with a view of what I'm looking to spit out automatically. The "All Lineups" tab contains what you see in the screenshot and a sample of where I am looking to get. In this instance. I have removed "Evan" from the 5 person set. This leaves Fent, Beeks, Hollis, Derek, Mendez. I have then placed each of the 8 possible combinations that can come with THAT specific set of 5 based on the potential variable of certain names have two builds. I would be looking to replicate that process for each 5 person scenario.

Hello. I need to calculate the average number of words in the cells for two columns to see if the average number of words they entered differs between two groups.

I believe I could just use the formula to count the number of words in each column (C and D) and divide it by the number of cells that contain words. But the problem is that the original data has over 6000 participants (i.e. 6000 rows) so it is highly likely that some participants just didn’t enter any words for some reason (see C15/D16, which are both empty). So if I were to just count the number of words in the column divided by cells with words, those participants wouldn’t be counted. There is also the matter of seemingly empty cells that might contain spaces.

Is there a way for me to count the average number of words for each group while still counting the participants that might not have entered any words?

I attached an example file since the original data is not to be shared, and is needlessly large for this purpose anyway, but that is approximately how the data looks. Each row in the example file represents one participant/individual, and each individual is only a part of one of two groups.

I actually don’t know if this is even possible to do in excel, but if it is, I would be extremely grateful for any help you could offer.

I believe I could just use the formula to count the number of words in each column (C and D) and divide it by the number of cells that contain words. But the problem is that the original data has over 6000 participants (i.e. 6000 rows) so it is highly likely that some participants just didn’t enter any words for some reason (see C15/D16, which are both empty). So if I were to just count the number of words in the column divided by cells with words, those participants wouldn’t be counted. There is also the matter of seemingly empty cells that might contain spaces.

Is there a way for me to count the average number of words for each group while still counting the participants that might not have entered any words?

I attached an example file since the original data is not to be shared, and is needlessly large for this purpose anyway, but that is approximately how the data looks. Each row in the example file represents one participant/individual, and each individual is only a part of one of two groups.

I actually don’t know if this is even possible to do in excel, but if it is, I would be extremely grateful for any help you could offer.