Hello

I am new to this forum and would love to get some help. I am trying to get all the times aligned and not getting it to work. Every time I fix one the other one fails. I am not good with rules as I am a beginner with excel.

I have two different formulas going right now and I am stuck. I could use the help. At the end of the schedule is a totals column for the weekly hours. The only one working right is the late column because it's not really tied to the bigger cells. I need the late column to minus hours from the weekly total hrs of 37.5 per week.

I was hoping to get this going for Monday at the beginning of the shift. Hope someone can assist me. The headings in red are the two different formulas.

Need: The total hrs worked column to deduct the 30 min lunch. If they stay an hour of OT it will show up as the below. The OT column to calculate the OT with 15 min increments. 1.25, 1.5, 1.75 etc.. depending on how long the employee stays back.

START END TOTAL HRS WRK OT LATE

7:00 AM 4:00 PM 8.5 1

I am new to this forum and would love to get some help. I am trying to get all the times aligned and not getting it to work. Every time I fix one the other one fails. I am not good with rules as I am a beginner with excel.

I have two different formulas going right now and I am stuck. I could use the help. At the end of the schedule is a totals column for the weekly hours. The only one working right is the late column because it's not really tied to the bigger cells. I need the late column to minus hours from the weekly total hrs of 37.5 per week.

I was hoping to get this going for Monday at the beginning of the shift. Hope someone can assist me. The headings in red are the two different formulas.

Need: The total hrs worked column to deduct the 30 min lunch. If they stay an hour of OT it will show up as the below. The OT column to calculate the OT with 15 min increments. 1.25, 1.5, 1.75 etc.. depending on how long the employee stays back.

START END TOTAL HRS WRK OT LATE

7:00 AM 4:00 PM 8.5 1

Hi to all

In column A there is this formatting

=ISNUMBER(SEARCH("2",F2))

=ISNUMBER(SEARCH("1",F2))

it should only work with positive and not negative numbers.

xam

In column A there is this formatting

=ISNUMBER(SEARCH("2",F2))

=ISNUMBER(SEARCH("1",F2))

it should only work with positive and not negative numbers.

xam

I am attempting to put together a spreadsheet that will store the details of players and anyone travelling with them.

The important part is that if they are sharing a room that each are registered with the correct package.

Once they have entered the name of the Hotel in the Hotel input page the 2 other dependant boxes display the package and then the length of stay.

I just wish for the last entry in the Keyed in List page, which is offset, to be checked and if different then the warning alert sign will ask them whether they wish

to correct the input or continue, otherwise the process will continue automatically.

The Validation form requires a formula that I have wrestled with I cannot formulate the "If, Or, and Exact". I have attached my sample workbook.

Any advice would be welcomed.

Thank you,

Jesse

Hotel input.xlsm

The important part is that if they are sharing a room that each are registered with the correct package.

Once they have entered the name of the Hotel in the Hotel input page the 2 other dependant boxes display the package and then the length of stay.

I just wish for the last entry in the Keyed in List page, which is offset, to be checked and if different then the warning alert sign will ask them whether they wish

to correct the input or continue, otherwise the process will continue automatically.

The Validation form requires a formula that I have wrestled with I cannot formulate the "If, Or, and Exact". I have attached my sample workbook.

Any advice would be welcomed.

Thank you,

Jesse

Hotel input.xlsm

Hello,

I was curious if anyone would have an alternate formula to use to match data in one column and count unique data in another column that wouldn't require an Array? I found the array formula below and it works, but my worksheet has 60,000 rows and this formula takes forever to calculate, which makes it impossible to use. I was hoping for an alternative formula that may not require as much computer resources and could provide the results in a timely fashion. Appreciate any suggestions/alternative formulas!

Array formula:

I was curious if anyone would have an alternate formula to use to match data in one column and count unique data in another column that wouldn't require an Array? I found the array formula below and it works, but my worksheet has 60,000 rows and this formula takes forever to calculate, which makes it impossible to use. I was hoping for an alternative formula that may not require as much computer resources and could provide the results in a timely fashion. Appreciate any suggestions/alternative formulas!

Array formula:

Code:

`=SUMPRODUCT((($A$3:$A$40000=A3))/COUNTIFS($A$3:$A$40000,$A$3:$A$40000&"",$B$3:$B$40000,$B$3:$B$40000&""))`

I want to use if,or and combination for deciding eligible members.

Conditions

1 if number is >= 20 eligible

or

2 if number is >= 15 & amount is >=70000 eligible

or

3 if number is >= 12 & amount is >=100000 eligible

can any one help me

Conditions

1 if number is >= 20 eligible

or

2 if number is >= 15 & amount is >=70000 eligible

or

3 if number is >= 12 & amount is >=100000 eligible

can any one help me

Hello Everyone,

So since The TEXTJOIN formula is incompatible with Excel 2016, I was wondering if there was an alternative formula or VBA code.

The formula I was using was {="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(risks[Likelihood]=$C3,IF(risks[Impact]=D$8,risks[Title],""),""))}

This formula plots the name of the risk on a chart depending on it's probability score and impact score. See attached for the visual. Textjoin would be

The formula allows for multiple risks to be inputted in a single cell.

Can this still be done without the TEXTJOIN Function?

risk-map (2).xlsx

So since The TEXTJOIN formula is incompatible with Excel 2016, I was wondering if there was an alternative formula or VBA code.

The formula I was using was {="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(risks[Likelihood]=$C3,IF(risks[Impact]=D$8,risks[Title],""),""))}

This formula plots the name of the risk on a chart depending on it's probability score and impact score. See attached for the visual. Textjoin would be

The formula allows for multiple risks to be inputted in a single cell.

Can this still be done without the TEXTJOIN Function?

risk-map (2).xlsx

Hello, I'm having issues with a spreadsheet I've been working on. This is a real estate proforma analysis spreadsheet that I downloaded from a site and then tailored to my use. That said, I have not messed with any of the major formulas. I have recently begun having some issues, and I hope someone can take a look at the spreadsheet and help. I also want to be able to modify the IRR calculations. Here are the items I'm hoping to solve:

1. On the Underwriting tab, cell I59, this is the equity and debt table, based off the total sources and uses above. I59 currently has a hard input of $13,500,000. However, I would like to be able to calculate the equity as 35% of the total uses in cell I56, but when I put this formula in I59 (=35%*I56), it never stops calculating, jumps all over the place and affects the entire spreadsheet. Please note: the Capitalized Construction Interest in cell I51 is directly calculated by the Construction Debt in cell I60. Also note: I have iterative calculations on, which I thought was the issue originally. Perhaps this worksheet has some macros calculations that are causing issues?

2. When I try to save this worksheet, it loads for a while as if it is trying to calculate something. Only when I press the "Esc" key it will pop up "calculation is incomplete. Recalculate before saving?" If I click no, it will save right away. How to avoid this?

3. This question is a bit more in depth. On the Underwriting tab, starting in row 220-221, there are some project-level IRR calculations, and starting in row 234, there are some waterfall IRR calculations. The problem is that this spreadsheet seems to assume that all equity comes in at the same time. However, the LP in row 237 will bring in capital at Month 13, where as the GP in row 236 will bring in capital at Month 0. There is a relatively straight-forward way to adjust the timing of the costs spent in the Sources and Uses table above starting in row 23, but I don't know of a good way to adjust the timing of which money comes in when. Furthermore, I want to be able to calculate IRR based on the money coming in at different times, if possible.

Thank you for taking a look at this! It won't let me upload the file here (perhaps it is too big?) so I've provided the dropbox link here. Thank you!

https://www.dropbox.com/s/izsa0x8avf...7.20.xlsm?dl=0

]]>1. On the Underwriting tab, cell I59, this is the equity and debt table, based off the total sources and uses above. I59 currently has a hard input of $13,500,000. However, I would like to be able to calculate the equity as 35% of the total uses in cell I56, but when I put this formula in I59 (=35%*I56), it never stops calculating, jumps all over the place and affects the entire spreadsheet. Please note: the Capitalized Construction Interest in cell I51 is directly calculated by the Construction Debt in cell I60. Also note: I have iterative calculations on, which I thought was the issue originally. Perhaps this worksheet has some macros calculations that are causing issues?

2. When I try to save this worksheet, it loads for a while as if it is trying to calculate something. Only when I press the "Esc" key it will pop up "calculation is incomplete. Recalculate before saving?" If I click no, it will save right away. How to avoid this?

3. This question is a bit more in depth. On the Underwriting tab, starting in row 220-221, there are some project-level IRR calculations, and starting in row 234, there are some waterfall IRR calculations. The problem is that this spreadsheet seems to assume that all equity comes in at the same time. However, the LP in row 237 will bring in capital at Month 13, where as the GP in row 236 will bring in capital at Month 0. There is a relatively straight-forward way to adjust the timing of the costs spent in the Sources and Uses table above starting in row 23, but I don't know of a good way to adjust the timing of which money comes in when. Furthermore, I want to be able to calculate IRR based on the money coming in at different times, if possible.

Thank you for taking a look at this! It won't let me upload the file here (perhaps it is too big?) so I've provided the dropbox link here. Thank you!

https://www.dropbox.com/s/izsa0x8avf...7.20.xlsm?dl=0

Dear All,

I hope someone can help me again.

In the sheet named "Salary", column A2 and A3.... contains an amount, a formula in column B2 should find the cell A2 in the sheet named "Medical Scale" and return the adjacent cell to the right. As i attached a sample file for reference.

Thank you in advance.

I hope someone can help me again.

In the sheet named "Salary", column A2 and A3.... contains an amount, a formula in column B2 should find the cell A2 in the sheet named "Medical Scale" and return the adjacent cell to the right. As i attached a sample file for reference.

Thank you in advance.

Hi everybody, i want to count every number except 0 and 2, and everytime after 2 apears i want to start counting again.

Hi Everyone,

Anyone have any ideas as to how I can tweak the following working formula to return only the sum of cells which are less than a given number.

The working formula as per my attached worksheet I have is: =SUMPRODUCT((B2:B20=17)*(D2:K20))

The formula is returning the sum of those values which match the criteria number 17 but I would only like the values which are less than "300" returned.

Therefore i would like the total figure in cell O2 to reflect as "110" and not "410"

I have attached a sample worksheet for your reference.

Many Thanks,

Bobby.

Anyone have any ideas as to how I can tweak the following working formula to return only the sum of cells which are less than a given number.

The working formula as per my attached worksheet I have is: =SUMPRODUCT((B2:B20=17)*(D2:K20))

The formula is returning the sum of those values which match the criteria number 17 but I would only like the values which are less than "300" returned.

Therefore i would like the total figure in cell O2 to reflect as "110" and not "410"

I have attached a sample worksheet for your reference.

Many Thanks,

Bobby.

I have a range of data with 5 columns, containing training courses. I want to create a single list of unique values. I would prefer to use UNIQUE if possible and not an array formula because there data set is very large and the array would be too slow. I don't mind if I need to use 5 different UNIQUE functions and then hide some columns to get my result. Is this possible?

For reference, I have named each range as Course1, Course2, Course3, etc.

Thanks in advance.

For reference, I have named each range as Course1, Course2, Course3, etc.

Thanks in advance.

Dear All,

I hope everyone is having a good day.

I had a previous question that was solved yesterday and now I'm having another issue because the table that the formula is referencing changed.

https://www.excelforum.com/excel-for...ml#post5429958

I have a sample excel file attached. In sheet named "salary" cell A2 contains an old salary and B2 contains the new salary. What it needs to find is the exact values of A2 and B2 in the other sheet named "Medical Scale" and returns the content of the Column heading (B1 to AC1) for Level and the content of the column heading (B2 to AC2) for the Grade, the value that should return is the highest value which is the new salary.

I hope anyone can assist me. Thank you in advance.

**I have reattached the correct file.

Corine

I hope everyone is having a good day.

I had a previous question that was solved yesterday and now I'm having another issue because the table that the formula is referencing changed.

https://www.excelforum.com/excel-for...ml#post5429958

I have a sample excel file attached. In sheet named "salary" cell A2 contains an old salary and B2 contains the new salary. What it needs to find is the exact values of A2 and B2 in the other sheet named "Medical Scale" and returns the content of the Column heading (B1 to AC1) for Level and the content of the column heading (B2 to AC2) for the Grade, the value that should return is the highest value which is the new salary.

I hope anyone can assist me. Thank you in advance.

**I have reattached the correct file.

Corine

Hello,

Please find the attached example workbook. I need to create a list from a chart.

Please find the attached example workbook. I need to create a list from a chart.

Hi,

I am trying to index column B of the file attached within a single cell based on criteria of column A. Is there a formula that we can use to do that? Column E is showing exactly how I want it to return.

I am trying to index column B of the file attached within a single cell based on criteria of column A. Is there a formula that we can use to do that? Column E is showing exactly how I want it to return.

I was wondering if there were a formula I could use to sort column A (which has many names listed like "John Smith" (first and last name only) into column B in alphabetical order by last name? I have found a couple using SORT, INDEX, SPLIT...but they don't work. Thanks for any help.

]]>