Hello,

Having terrible time trying to extract the month from a date, say, 21Sep17. Can't locate the right worksheet or VBA function to do this.

They all seem to want to go the other way - i.e. text to date. Anybody know an easy way to do this?

Edited Remark: Sorry, forgot to mention - I can use the Custom Format dialogue box in Excel and choose "mmm" for the format,

but the results are still a date with only the month showing in the cell. When I try to get that month into a text variable, I still end

up with a date, not a text string.

Thanks,

newbieobjectcoder

]]>Having terrible time trying to extract the month from a date, say, 21Sep17. Can't locate the right worksheet or VBA function to do this.

They all seem to want to go the other way - i.e. text to date. Anybody know an easy way to do this?

Edited Remark: Sorry, forgot to mention - I can use the Custom Format dialogue box in Excel and choose "mmm" for the format,

but the results are still a date with only the month showing in the cell. When I try to get that month into a text variable, I still end

up with a date, not a text string.

Thanks,

newbieobjectcoder

Hello -

I currently have the following formula

What it is doing is based on a zip code input pulling a subsidy rate associated. It currently works as expected and needs to remain functional. However, I need to do is add a bit of complexity to it. I'm hoping someone can help.

Cell C8 is a field that is used for the user to enter their zip-code

X15:X22 - 93620, 93635, 95301, 95340, 95726, 95945, 95949, 95959, 95975

If C4 equals "EE_Only" and C8 equals X15:X22 than I should get "404.67". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Spouse" and C8 equals X15:X22 than I should get "832.41". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Child(ren)" and C8 equals X15:X22 than I should get "624.81". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Family" and C8 equals X15:X22 than I should get "1139.54". If both of these criteria are not meet the referenced code should run.

Any help is greatly appreciated.

]]>I currently have the following formula

Code:

`=INDEX('All Other Subsidy'!F10:I32,MATCH('Rate Testing'!C58,'All Other Subsidy'!A10:A32,0),MATCH(input_tier_medical,'All Other Subsidy'!F9:I9,0))`

Cell C8 is a field that is used for the user to enter their zip-code

X15:X22 - 93620, 93635, 95301, 95340, 95726, 95945, 95949, 95959, 95975

If C4 equals "EE_Only" and C8 equals X15:X22 than I should get "404.67". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Spouse" and C8 equals X15:X22 than I should get "832.41". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Child(ren)" and C8 equals X15:X22 than I should get "624.81". If both of these criteria are not meet the referenced code should run.

If C4 equals "EE+Family" and C8 equals X15:X22 than I should get "1139.54". If both of these criteria are not meet the referenced code should run.

Any help is greatly appreciated.

I have a nagging problem in my Office for Mac 2016, would appreciate your help. When I select a group of cells it toggles the dashed line frame around it to indicate it is being selected. But in two seconds the frame turns back to normal and I can no longer paste. That is extremely inconvenient, as it makes me rush to paste before the frame is turned off . Ideas?

]]>hello

can anybody explain me why the sum amount is difference.

can anybody explain me why the sum amount is difference.

Hey people,

I want to run a Monte Carlo simulation with the following aim: see the impact of missing a certain number of random days in a market index over 20 years.

I have the daily returns over that period handy and would like the simulation 10,000 times with missing 5,10,15,20,25... random days and going into a fixed deposit instead during the missed days. Then finding out what my average return and risk would be compared to not missing those days.

I am new to trying a Monte Carlo simulation in excel and just did a tutorial on doing a dice simulation. That was easy enough, but didn't help too much with my above problem.

So 20 years is 5027 rows (weekends are excluded). see table so the first column contains the dates (each day on the index), the second column is the closing price of the index, the third column is the gain or loss for the days (based on today and yesterday's ending value) and the 4th column is a simple start at 100 and add each day's gain/loss.

so i need that exact table (which goes until Aug 2017 which is 5027 rows) with random days left out and then seeing what the final value is in the 4th column when a set number of days are randomly removed. So then i can compare the average ending value when 5 random days are removed, simulated over 10000 times, with the ending value when no days are removed.

Here is the study i'm trying to duplicate for context pages 11 and 16 explain the results and a bit of the methodology respectively. I am using days, instead of months as in the above, due to the index i'm looking at not having that long track record. rest is identical to the above.

Any help would be amazing, also let me know if it's too much of a project to tackle.

]]>I want to run a Monte Carlo simulation with the following aim: see the impact of missing a certain number of random days in a market index over 20 years.

I have the daily returns over that period handy and would like the simulation 10,000 times with missing 5,10,15,20,25... random days and going into a fixed deposit instead during the missed days. Then finding out what my average return and risk would be compared to not missing those days.

I am new to trying a Monte Carlo simulation in excel and just did a tutorial on doing a dice simulation. That was easy enough, but didn't help too much with my above problem.

So 20 years is 5027 rows (weekends are excluded). see table so the first column contains the dates (each day on the index), the second column is the closing price of the index, the third column is the gain or loss for the days (based on today and yesterday's ending value) and the 4th column is a simple start at 100 and add each day's gain/loss.

so i need that exact table (which goes until Aug 2017 which is 5027 rows) with random days left out and then seeing what the final value is in the 4th column when a set number of days are randomly removed. So then i can compare the average ending value when 5 random days are removed, simulated over 10000 times, with the ending value when no days are removed.

Here is the study i'm trying to duplicate for context pages 11 and 16 explain the results and a bit of the methodology respectively. I am using days, instead of months as in the above, due to the index i'm looking at not having that long track record. rest is identical to the above.

Any help would be amazing, also let me know if it's too much of a project to tackle.

I have a large macro that I will run and leave the computer on and just let it complete it can take hours to do. The problem arises if I choose another window or another window pops up and becomes active the macro will stop running until I make excel the active window again and it will take off. How can i get it to run no matter if its the active window or not i am using Excel 2013.

Thank you for your time.

]]>Thank you for your time.

Hello -

I have a spread sheet that has multiple carriers, metallics and rates. I'm looking to find the lowest carrier among bronze rates.

See attached.

I have a spread sheet that has multiple carriers, metallics and rates. I'm looking to find the lowest carrier among bronze rates.

See attached.

My daughter phoned me with a problem which I could not fix!

After a recent update she discovered that Excel was not working as it should do, for example it would not add up 2 cells properly and instead of showing full stop seperators it was showing commas. I thought that this could be sorted by a visit to the options but not so. Has anyone else had this problem or offer any advise.

]]>After a recent update she discovered that Excel was not working as it should do, for example it would not add up 2 cells properly and instead of showing full stop seperators it was showing commas. I thought that this could be sorted by a visit to the options but not so. Has anyone else had this problem or offer any advise.

I have a workbook with 2 sheets work and summary.

I have defined the ranges in sheet work .

the summary is a consolidation of data from sheet work .

But when I make the formula from sheet summary , the defined name does not appear when doing the formula .

my question can defined ranges from a sheet be used in a formula in another sheet .

In same sheet define name works when I consolidate the formula but on another sheet in same work book.

Can someone advise or should I use table nomenclature

]]>I have defined the ranges in sheet work .

the summary is a consolidation of data from sheet work .

But when I make the formula from sheet summary , the defined name does not appear when doing the formula .

my question can defined ranges from a sheet be used in a formula in another sheet .

In same sheet define name works when I consolidate the formula but on another sheet in same work book.

Can someone advise or should I use table nomenclature

I am a teacher. I have a spreadsheet which I keep our school data on.

On one worksheet (named: Cohort Analysis), I want to count up the amount of pupils in the school who are on their target level or above.

It gets complicated because, depending which year there is makes a difference to their target level. I also need to track the number of children who are more than a whole level above their target level.

The worksheet named Code, has the levels.

Can anyone devise a formula that can do the relevant addition? My attempts haven't worked. Delete what you want and re-attach if you can do it.

I've attached a simplified version of the spreadsheet.

Thanks in advance

On one worksheet (named: Cohort Analysis), I want to count up the amount of pupils in the school who are on their target level or above.

It gets complicated because, depending which year there is makes a difference to their target level. I also need to track the number of children who are more than a whole level above their target level.

The worksheet named Code, has the levels.

Can anyone devise a formula that can do the relevant addition? My attempts haven't worked. Delete what you want and re-attach if you can do it.

I've attached a simplified version of the spreadsheet.

Thanks in advance

Hi Excel Forum,

I am hoping someone can assist me.

I am trying to split the total sum (Cell J13) proportionally according to the % of the individual costs (column E) & also the Duty % (Column H).

I have tried to do this by using the % value (column F) and it will work providing the % values are all the same in (column H)

The problem is when the % values are not the same in (column H)

I have tried to use the % values (column M) from the data in (Column F & I) the total sum is correct but the duty cost % split per item is not correct.

Could some one assist in how I can factor in the Duty % (Column H) to the formula in (column K) or what needs to be changed in the formula in (column M) in order to split the costs proportionally to each item according to the item value & duty %.

I have attached the file which is only currently using the Total price % split (column F) to calculate the split.

Apologizes in advance if I've not done something correctly on this post as this is my first post.

Thank you in advance.

Ian

I am hoping someone can assist me.

I am trying to split the total sum (Cell J13) proportionally according to the % of the individual costs (column E) & also the Duty % (Column H).

I have tried to do this by using the % value (column F) and it will work providing the % values are all the same in (column H)

The problem is when the % values are not the same in (column H)

I have tried to use the % values (column M) from the data in (Column F & I) the total sum is correct but the duty cost % split per item is not correct.

Could some one assist in how I can factor in the Duty % (Column H) to the formula in (column K) or what needs to be changed in the formula in (column M) in order to split the costs proportionally to each item according to the item value & duty %.

I have attached the file which is only currently using the Total price % split (column F) to calculate the split.

Apologizes in advance if I've not done something correctly on this post as this is my first post.

Thank you in advance.

Ian

Hello,

I'd like to create a workbook detailing individual snags on equipment. Each snag has its own worksheet detailing the actions taken and I'd like to summarise this information in another worksheet called 'Summary'. I'd like the summary to update automatically as each snag's worksheet is updated and I've done this for snag 001. However, I want the information in each row of the summary sheet to be linked to the snag reference in column A - making the input process much quicker.

In conclusion, if I changed the snag reference in cell A2 from 001 to 002, I'd like the corresponding cells in row 2 to reflect the values in worksheet 002 and not 001.

Is there a way of doing this?

Thanks,

I'd like to create a workbook detailing individual snags on equipment. Each snag has its own worksheet detailing the actions taken and I'd like to summarise this information in another worksheet called 'Summary'. I'd like the summary to update automatically as each snag's worksheet is updated and I've done this for snag 001. However, I want the information in each row of the summary sheet to be linked to the snag reference in column A - making the input process much quicker.

In conclusion, if I changed the snag reference in cell A2 from 001 to 002, I'd like the corresponding cells in row 2 to reflect the values in worksheet 002 and not 001.

Is there a way of doing this?

Thanks,

Hello,

How to search multiple text strings in array. This one works:

=SUMPRODUCT(ISERR(SEARCH("Kim Jong-un",$D$8:$D$1440))*($E$8:$L$1440)*ISERR(SEARCH("Asteroid2012TC4",$D$8:$D$1440)))

This one sum all except Kim's and October12 asteroid.

But how come this one doesn't work:

=SUMPRODUCT(ISERR(SEARCH({"Kim Jong-un","Asteroid2012TC4"},$D$8:$D$1440))*($E$8:$L$1440))

This gives wrong result.

Thanks!

]]>How to search multiple text strings in array. This one works:

=SUMPRODUCT(ISERR(SEARCH("Kim Jong-un",$D$8:$D$1440))*($E$8:$L$1440)*ISERR(SEARCH("Asteroid2012TC4",$D$8:$D$1440)))

This one sum all except Kim's and October12 asteroid.

But how come this one doesn't work:

=SUMPRODUCT(ISERR(SEARCH({"Kim Jong-un","Asteroid2012TC4"},$D$8:$D$1440))*($E$8:$L$1440))

This gives wrong result.

Thanks!

IN THE ATTACHED FILE CONDITIONAL FORMATING WITH FORMUL IS APPLIED IN COLUMN A & B

WHEN I ENTERED SAME FIGURE IN COLUMN A & B THEN IT'S COLOUR BECOME RED

BUT WHEN I INSERT SAME FIGURE TWO TIME IN COLUMN A THAT'S ALSO BECOME RED

I DON'T WANT THAT , ONLY COMPARE FIGURE OF A COLUMN WITH B COLUMN

you can see example of value 544.3 in attached it means 544.3 in a column A become red only

one time

WHEN I ENTERED SAME FIGURE IN COLUMN A & B THEN IT'S COLOUR BECOME RED

BUT WHEN I INSERT SAME FIGURE TWO TIME IN COLUMN A THAT'S ALSO BECOME RED

I DON'T WANT THAT , ONLY COMPARE FIGURE OF A COLUMN WITH B COLUMN

you can see example of value 544.3 in attached it means 544.3 in a column A become red only

one time