Hi

in excel, i have a text in one cell as below.

(1) ABCDE, (2)FQHIJ, (3)KLMNO, (4)PRQS, (5)TUVWXYZ

i want the result as below

ABCDE

FQHIJ

KLMNO

PRQS

TUVWXYZ

it seems the formula is to look for the text between "(1)" and the comma ","by using LEFT, MID, FIND. Other cell is text between ""(2)" and the comma and so on.

But i am still not clear about the formula exactly.

thanks a lot.

]]>in excel, i have a text in one cell as below.

(1) ABCDE, (2)FQHIJ, (3)KLMNO, (4)PRQS, (5)TUVWXYZ

i want the result as below

ABCDE

FQHIJ

KLMNO

PRQS

TUVWXYZ

it seems the formula is to look for the text between "(1)" and the comma ","by using LEFT, MID, FIND. Other cell is text between ""(2)" and the comma and so on.

But i am still not clear about the formula exactly.

thanks a lot.

Hi All, newbie here seeking some advice

Attached is a schedule calculator I use for running shift scenarios, I punch the requirements from forecasting team into ‘calculator ‘ tab and then start building schedules on master tab. This is a simple ‘WhatIF’ calculator only problem is it requires a hugh amount of maintenance as I need to add new shift opens, example I have a 2pm-5:45pm shift option but not 2pm-6pm so to add I need to go in and add the shift and manually update the calculator. Wondering if anyone uses something similar? I would like to build a sheet that would allow any possible shift work hours. Haven’t been able to find anything so far.

Thanks in advance

Attached is a schedule calculator I use for running shift scenarios, I punch the requirements from forecasting team into ‘calculator ‘ tab and then start building schedules on master tab. This is a simple ‘WhatIF’ calculator only problem is it requires a hugh amount of maintenance as I need to add new shift opens, example I have a 2pm-5:45pm shift option but not 2pm-6pm so to add I need to go in and add the shift and manually update the calculator. Wondering if anyone uses something similar? I would like to build a sheet that would allow any possible shift work hours. Haven’t been able to find anything so far.

Thanks in advance

Hi

I'm trying to use the LOOKUP function featuring multiple criteria to return a value from the previous occasion where both criteria are TRUE

Please have a look at the screen prints attached.

On this occassion there is just 1 TeamID running through the spreadsheet. But sometimes there are multiple teams.

So I've put in a LOOKUP function with two criteria, one of them being TeamID

Sometimes the formula gives the right answer, other times it gives the wrong answer

Why is this? It's worrying that the answer changes when the formula and inputs are the same.

Calculation mode is set to Automatic and ErrorChecking doesn't say that there are any circular references.

Another thing I've noticed is that sometimes the answer changes purely as a result of me filtering column(s).

**Update**

I've just created an identical scenario on a fresh spreadsheet on a different computer and even then I'm seeing the same problem.

I've uploaded the spreadsheet so you can see. The cell highlighted yellow is the one giving the wrong result.

Also notice that if you go through Evaluate Formula, it comes up with a different answer (the correct answer) to the answer displayed in the cell.

Thanks

I'm trying to use the LOOKUP function featuring multiple criteria to return a value from the previous occasion where both criteria are TRUE

Please have a look at the screen prints attached.

On this occassion there is just 1 TeamID running through the spreadsheet. But sometimes there are multiple teams.

So I've put in a LOOKUP function with two criteria, one of them being TeamID

Sometimes the formula gives the right answer, other times it gives the wrong answer

Why is this? It's worrying that the answer changes when the formula and inputs are the same.

Calculation mode is set to Automatic and ErrorChecking doesn't say that there are any circular references.

Another thing I've noticed is that sometimes the answer changes purely as a result of me filtering column(s).

I've just created an identical scenario on a fresh spreadsheet on a different computer and even then I'm seeing the same problem.

I've uploaded the spreadsheet so you can see. The cell highlighted yellow is the one giving the wrong result.

Also notice that if you go through Evaluate Formula, it comes up with a different answer (the correct answer) to the answer displayed in the cell.

Thanks

Given a spreadsheet that contains a number of rows with a category and a number, such as:

category 1 20,000

I want to run that against the below table:

100 200 300 400 500 600

category 1 6,250 15,000 30,000 60,000 90,000 175,000

category 2 5,500 15,000 34,000 70,000 120,000 250,000

category 3 1600 1,500 3,000 6,500 8,000 11,000

category 4 12,000 35,000 65,000 100,000 150,000 215,000

Now in this case I want to compare that number to the range of values in the table for category 1. So in this case it matched category 1, now it needs to cycle through those values and if less than the number higher than it, return the header number at the top of the table for than range it falls in. So in this case the number is less than 30,000 for that category, and so it would return the header of 200 in the table. Hopefully you see how that works. I am able to grind out a series of IF statements that logically works, but with my table size it far exceeds the 64 nested ifs limit. I've played around with MATCH and INDEX but can't figure out how to get it to properly act on the entire table.

]]>category 1 20,000

I want to run that against the below table:

100 200 300 400 500 600

category 1 6,250 15,000 30,000 60,000 90,000 175,000

category 2 5,500 15,000 34,000 70,000 120,000 250,000

category 3 1600 1,500 3,000 6,500 8,000 11,000

category 4 12,000 35,000 65,000 100,000 150,000 215,000

Now in this case I want to compare that number to the range of values in the table for category 1. So in this case it matched category 1, now it needs to cycle through those values and if less than the number higher than it, return the header number at the top of the table for than range it falls in. So in this case the number is less than 30,000 for that category, and so it would return the header of 200 in the table. Hopefully you see how that works. I am able to grind out a series of IF statements that logically works, but with my table size it far exceeds the 64 nested ifs limit. I've played around with MATCH and INDEX but can't figure out how to get it to properly act on the entire table.

Hi Folks

This is a quick post.

When I input say:

email@email.co.uk in the cell its reverts to a hyperlink which I dont want. How do I prevent this and I just want to show it as text ONLY?

Thanks

]]>This is a quick post.

When I input say:

email@email.co.uk in the cell its reverts to a hyperlink which I dont want. How do I prevent this and I just want to show it as text ONLY?

Thanks

In the attached spreadsheet i need a function to calculate the gross income required to afford the mortgage payment based on the parameters of the house purchase price.

Assuming my monthly housing costs should not exceed 32% of my gross income, what function can I use to calculate the required gross income.

Any help will be greatly appreciated!

Assuming my monthly housing costs should not exceed 32% of my gross income, what function can I use to calculate the required gross income.

Any help will be greatly appreciated!

I have a list of items. Each one has variations that make the "next higher level" cost slightly more. The increase between them is rarely the same and I need to see some statistics on those numbers.

I'd like paste a formula in a column next to the column of prices.

I can sort the prices by item then price again - so the sequence of prices would decrease as opposed to increase.

The only thing I can think of is to create a formula that finds the difference of a number and the number above it while ignoring non-positive numbers. The only problem I can see is if the last price of a product (the highest number) is less than the cheapest version of the next item. I don't know if there's a way to handle that - each product can have a variable amount of options so I can't make a forumla or set of formulas care about how many steps up its taken already.

Here are some numbers in the example:

682

727

824

869

914

959

1004

1049

1094

682

727

824

869

914

959

1004

1049

1094

339

355

401

417

433

449

465

481

497.........

Any help you can provide would be greatly appreciated.

]]>I'd like paste a formula in a column next to the column of prices.

I can sort the prices by item then price again - so the sequence of prices would decrease as opposed to increase.

The only thing I can think of is to create a formula that finds the difference of a number and the number above it while ignoring non-positive numbers. The only problem I can see is if the last price of a product (the highest number) is less than the cheapest version of the next item. I don't know if there's a way to handle that - each product can have a variable amount of options so I can't make a forumla or set of formulas care about how many steps up its taken already.

Here are some numbers in the example:

682

727

824

869

914

959

1004

1049

1094

682

727

824

869

914

959

1004

1049

1094

339

355

401

417

433

449

465

481

497.........

Any help you can provide would be greatly appreciated.

I figure there's a pretty simple formula to resolve this.

I would like Excel to Sum only 1 non-blank cell per row, based on a column priority.

Column Priority in the sample below is E/C/D/A/B

So, the process would be:

Excel checks E2 (blank), then moves to C2 (blank), then moves to D2 (3) and stops.

Then in the next row, it checks E3 (blank), then moves to C3 (13) and stops.

In the 3rd row, it checks E4 (8) and stops.

In row 4, it checks E5 (1) and stops.

In row 5, it checks E6 (blank), then moves to C6 (11) and stops.

In row 6, it checks E7 (blank), then C7 (blank), then to D7 (blank), then to A7 (blank), then to B7 (7) and stops.

Excel adds 3+13+8+1+11+7 = 43

Your help with this is much appreciated!

]]>I would like Excel to Sum only 1 non-blank cell per row, based on a column priority.

Column Priority in the sample below is E/C/D/A/B

So, the process would be:

Excel checks E2 (blank), then moves to C2 (blank), then moves to D2 (3) and stops.

Then in the next row, it checks E3 (blank), then moves to C3 (13) and stops.

In the 3rd row, it checks E4 (8) and stops.

In row 4, it checks E5 (1) and stops.

In row 5, it checks E6 (blank), then moves to C6 (11) and stops.

In row 6, it checks E7 (blank), then C7 (blank), then to D7 (blank), then to A7 (blank), then to B7 (7) and stops.

Excel adds 3+13+8+1+11+7 = 43

a | b | c | d | e |

12 | 10 | 3 | ||

11 | 6 | 13 | ||

6 | 8 | 8 | ||

9 | 25 | 1 | ||

5 | 17 | 11 | 8 | |

7 |

Your help with this is much appreciated!

Dear Excel Forum Member's

I have Excel Sheet I want Excel formula for Debit,Credit,Remaing Balance I want to show Remaing balance E6 and Total Balance E42 i hope you understant what i meant to say. Please find attached file..... BANK CHEQUE CASH DIPOSIT DETAILS 15.08.2019.xlsm

Thanks

]]>I have Excel Sheet I want Excel formula for Debit,Credit,Remaing Balance I want to show Remaing balance E6 and Total Balance E42 i hope you understant what i meant to say. Please find attached file..... BANK CHEQUE CASH DIPOSIT DETAILS 15.08.2019.xlsm

Thanks

I have a column of keywords

I want to add '+' too all words in the column for example

1. Nice here

2. bad here

3. ok man

4. no way

to becomes

1. +Nice +here

2. +bad +here

3. +ok +man

4. +no +way

How ?

]]>I want to add '+' too all words in the column for example

1. Nice here

2. bad here

3. ok man

4. no way

to becomes

1. +Nice +here

2. +bad +here

3. +ok +man

4. +no +way

How ?

Hello!

I am new to this forum and often run Excel projects for my boss - this forum may come in handy when my Googling tips just aren't cutting it and I would be forever grateful!

So our spreadsheet, on a weekly basis, reports the workload of our Associates - from incoming emails, phone calls, meetings, etc. From there, we count how many staff members were present for all 5 days of the work week, or if any took a single-day or full week of PTO, to 'normalize' the data.

My boss then told me there is a formula I can use to rid the #DIV/0! portions of the spreadsheet when there is a zero in the data. I looked and found the IFERROR function, but this formula is so long, I just don't know where exactly to put it! My boss suggested I wrap in IFERROR with the formula but Excel either says it's too little, missing a parenthesis, or just doesn't make sense.

Attached is a snippet of the table with the formula I need to wrap the IFERROR around. I can attach the spreadsheet if necessary, but figured a snippet might help since it's such a simple formula, I'm just having trouble finding where to put the IFERROR formula.

Thank you for any help!

Nichole

I am new to this forum and often run Excel projects for my boss - this forum may come in handy when my Googling tips just aren't cutting it and I would be forever grateful!

So our spreadsheet, on a weekly basis, reports the workload of our Associates - from incoming emails, phone calls, meetings, etc. From there, we count how many staff members were present for all 5 days of the work week, or if any took a single-day or full week of PTO, to 'normalize' the data.

My boss then told me there is a formula I can use to rid the #DIV/0! portions of the spreadsheet when there is a zero in the data. I looked and found the IFERROR function, but this formula is so long, I just don't know where exactly to put it! My boss suggested I wrap in IFERROR with the formula but Excel either says it's too little, missing a parenthesis, or just doesn't make sense.

Attached is a snippet of the table with the formula I need to wrap the IFERROR around. I can attach the spreadsheet if necessary, but figured a snippet might help since it's such a simple formula, I'm just having trouble finding where to put the IFERROR formula.

Thank you for any help!

Nichole

Hi Folks

I have a list of costs and a target margin % that I want to achieve. I have a formula & this determines how much I should charge to achieve this margin - so this is all good.

However what I want to work out now is a formula to provide a target margin assuming a discount of say 10% is added to the figure I charge.

I have attached an excel file showing the first part I described, which shows my 3 costs (A, B & C) and the target margin I want to achieve of 40% (cell J4), this in turn works out how much I should charge to achieve this profit margin (cell J5), so this is all good.

What I am trying to incorporate now is that the amount to charge figure will provide a discount of 10% to the customer but I still want to achieve 40% profit margin.

So looking at the file if I added a 10% discount to the €2,343, I would only charge the customer €2,109 (€2,343 - 10%) and in turn this would only giv me a profit margin of 33.3%. So I want a formula in the "amount to charge" cell that will incorporate a 10% discount on the price charged to the customer but also still give a profit margin of 40%.

Hope this is clear and/or possible.

Thanks

I have a list of costs and a target margin % that I want to achieve. I have a formula & this determines how much I should charge to achieve this margin - so this is all good.

However what I want to work out now is a formula to provide a target margin assuming a discount of say 10% is added to the figure I charge.

I have attached an excel file showing the first part I described, which shows my 3 costs (A, B & C) and the target margin I want to achieve of 40% (cell J4), this in turn works out how much I should charge to achieve this profit margin (cell J5), so this is all good.

What I am trying to incorporate now is that the amount to charge figure will provide a discount of 10% to the customer but I still want to achieve 40% profit margin.

So looking at the file if I added a 10% discount to the €2,343, I would only charge the customer €2,109 (€2,343 - 10%) and in turn this would only giv me a profit margin of 33.3%. So I want a formula in the "amount to charge" cell that will incorporate a 10% discount on the price charged to the customer but also still give a profit margin of 40%.

Hope this is clear and/or possible.

Thanks

Hello All

I was wondering if a SUMIF function could be use to solve this problem I am having or do I have to use a macro of some sort?

I have people who are assigned to work in a specific departments .The Bolded Text is the Dept (cell A1 ) and the roster of employees fall under that bolded text Cells A2-A8)

I would like to have the persons name added to the dept name in coulmn B and at the start of a new Depratment the formula would have to know to add the Name of the employee to the next Bolded Department.

I attempted to start this using a simple formula, but it would take me forever, and was wondering if SUMIF could help?

Thanks in advance

I was wondering if a SUMIF function could be use to solve this problem I am having or do I have to use a macro of some sort?

I have people who are assigned to work in a specific departments .The Bolded Text is the Dept (cell A1 ) and the roster of employees fall under that bolded text Cells A2-A8)

I would like to have the persons name added to the dept name in coulmn B and at the start of a new Depratment the formula would have to know to add the Name of the employee to the next Bolded Department.

I attempted to start this using a simple formula, but it would take me forever, and was wondering if SUMIF could help?

Thanks in advance

Hi All,

This is a tricky one to explain but i'll try my best (working example attached albeit not automated).

So i'm using the weekday formula to allocate hours worked per day into weekly columns via sumif's - this is working ok.

Now the challenge comes in for weeks that falls over a calendar month end/start.

I'd like to be able to split out the day's within the week which straddles the two months, as per my example 2 days in Apr and 3 days in May. This would mean that the last week of apr will be light and the first week of may will be heavy.

Hope this makes sense and still toying with the idea of using this method or not but keen to hear your thoughts on this problem

This is a tricky one to explain but i'll try my best (working example attached albeit not automated).

So i'm using the weekday formula to allocate hours worked per day into weekly columns via sumif's - this is working ok.

Now the challenge comes in for weeks that falls over a calendar month end/start.

I'd like to be able to split out the day's within the week which straddles the two months, as per my example 2 days in Apr and 3 days in May. This would mean that the last week of apr will be light and the first week of may will be heavy.

Hope this makes sense and still toying with the idea of using this method or not but keen to hear your thoughts on this problem

I have fruits. vegetables, meat, poultry and seafood. I already have a drop-down for that in the cell B8. Now I need to select a product based on the category. I tried Index and Match before but I get an error. I don't want to do it with named ranges so it has to be the formula. Hopefully, you can help.