# Posts by MysticGenius

• ## Diminishing Returns/Optimal Spend

Hi,

I have two columns of data, one is spend, the second is Goals achieved for that level of spend. As you can see there is no linear pattern to the goals vs spend.

Can someone give me a formula to calculate the optimal spend?

Thanks

Spend Goals [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 101"]\$4,220[/TD]
[TD="width: 57"]30[/TD]

[/tr]

[tr]

[td]

\$4,250

[/td]

[td]

22

[/td]

[/tr]

[tr]

[td]

\$4,252

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

\$4,266

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

\$4,334

[/td]

[td]

22

[/td]

[/tr]

[tr]

[td]

\$4,362

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

\$4,376

[/td]

[td]

4

[/td]

[/tr]

[tr]

[td]

\$4,380

[/td]

[td]

15

[/td]

[/tr]

[tr]

[td]

\$4,381

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,392

[/td]

[td]

30

[/td]

[/tr]

[tr]

[td]

\$4,437

[/td]

[td]

22

[/td]

[/tr]

[tr]

[td]

\$4,447

[/td]

[td]

33

[/td]

[/tr]

[tr]

[td]

\$4,465

[/td]

[td]

15

[/td]

[/tr]

[tr]

[td]

\$4,479

[/td]

[td]

22

[/td]

[/tr]

[tr]

[td]

\$4,527

[/td]

[td]

4

[/td]

[/tr]

[tr]

[td]

\$4,537

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,544

[/td]

[td]

33

[/td]

[/tr]

[tr]

[td]

\$4,574

[/td]

[td]

26

[/td]

[/tr]

[tr]

[td]

\$4,576

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,629

[/td]

[td]

30

[/td]

[/tr]

[tr]

[td]

\$4,634

[/td]

[td]

41

[/td]

[/tr]

[tr]

[td]

\$4,658

[/td]

[td]

37

[/td]

[/tr]

[tr]

[td]

\$4,660

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,662

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

\$4,665

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,685

[/td]

[td]

82

[/td]

[/tr]

[tr]

[td]

\$4,687

[/td]

[td]

26

[/td]

[/tr]

[tr]

[td]

\$4,700

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,704

[/td]

[td]

26

[/td]

[/tr]

[tr]

[td]

\$4,715

[/td]

[td]

33

[/td]

[/tr]

[tr]

[td]

\$4,728

[/td]

[td]

15

[/td]

[/tr]

[tr]

[td]

\$4,783

[/td]

[td]

30

[/td]

[/tr]

[tr]

[td]

\$4,795

[/td]

[td]

30

[/td]

[/tr]

[tr]

[td]

\$4,801

[/td]

[td]

26

[/td]

[/tr]

[tr]

[td]

\$4,807

[/td]

[td]

56

[/td]

[/tr]

[tr]

[td]

\$4,849

[/td]

[td]

19

[/td]

[/tr]

[tr]

[td]

\$4,859

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

\$4,865

[/td]

[td]

33

[/td]

[/tr]

[/TABLE]

• ## Converting US to UK dates

Re: Converting US to UK dates

I found a solution:

Code
``=IFERROR((MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))+0,A2)``
• ## Converting US to UK dates

Hi,

I have a column of dates which has 2 formats in it for some reason half are UK and half are US.

I want to change them all to UK format.

I have tried to use:

Code
``=IF(MID(A2,4,2)>=13,(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))+0,IFERROR(MID(A2,4,2),A2))``

and this converts the US dates to the UK, but it gives me #value when it hits the UK formatted date rather than just using the Date as It is.

Please see the attached sample.

Thanks

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open Word docs in a folder, enter password and save with password removed

Quote from cytop;756016

Sorry about that - one of those "Nah - too simple to error" errors that wasn't tested. Happened when it tried to process a file in a sub-directory. Fix attached.

thats absolutely perfect!! thanks for all your help!!!

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open Word docs in a folder, enter password and save with password removed

That looks amazing!

I got this error:

[ATTACH=CONFIG]66868[/ATTACH]

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755923

I know it'll fail - it wasn't written to accomodate sub-directories in multiple folders.

Answering queries on forums like this is a little like electricity - the answers always take the path of least resistance. Sometimes (like now), the unmentioned requirement can radically affect the solution. It needs a bit of a re-write.

Judging by the log you posted, I guess it's after 6:00pm and home time. There'll be something for you tomorrow.

Thank you muchly sir

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755921

... what about the sub-directories?

it failed with the sub dirs,

i got this output:

2015Sep08 18:06:22 Started
2015Sep08 18:06:29 Directory exists
2015Sep08 18:06:29 Finished

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755915

Nah, we all do things like that at times. But can I reserve the right to quote you in future?

Adding code in Word is the same as Excel, Alt-F11 for the VBA environment and just paste the code, cursor somewhere in the code and press F5.

Yeah,

u can quote me on that in the future!!

I have one last question for you, how can i attach the VBA to a command button in word, so I dont have to jump into developer to run it!

I have the command button in my document but i cant seem to assign a macro to it like i can with excel.

TIA

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

It worked perfect!!!

Thanks so much

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755913

Are you running this in Word or Excel...?

excel.....im guessing it should be work......

Im understanding your comment "Word VBA"

I feel like such a dumbass!!!!

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Hi,

This is the output i get:

2015Sep08 17:27:01 Started
2015Sep08 17:27:13 Directory exists
2015Sep08 17:27:13
2015Sep08 17:27:13 File: ICTNWK505_AMa.doc
2015Sep08 17:27:23 ActiveX component can't create object
2015Sep08 17:27:23 Finished

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755902

Comment out the line

Code
``' If Err.Number = 4605 Then Resume ResumePoint``

by adding an Apostrophe at the beginning of the line, then see if any messages are displayed.

Hi,

thanks, no errors are displayed.

Is there possible a referance library i need to enable?

I have the following:

Visual Basic For Applications
MS Excel 14.0 Object Library
OLE Automation
MS Office 14.0 Object Library
MS Word 14.0 Object Library
Windows Script Host Object Model

Quote from cytop;755902

It's pointless trying to expand this until it works for 1 directory but you really should have mentioned that to begin with as it will need to be rewritten.

Apologies, i was told that the documents were all in one folder, however they didnt tell me they were in sub directories.

Apologies again!

Thanks for the help

• ## Open Word docs in a folder, enter password and save with password removed

Re: Open wod docs in a folder, enter password and save with password removed

Quote from cytop;755862

Word VBA...

There seems to be an issue, It allows me to enter the password, It creates the "Saved" Folder but nothing gets placed into the saved folder.

Also is there any way to expand it to look in a folder with sub folders?

• ## Open Word docs in a folder, enter password and save with password removed

Hi,

I have a situation, an employee has left and gone overseas. I have about 200+ word documents that i need to open and remove the password from.

I have the password, and all of the documents are in the same directory, what i need to do is:

1: Open the XXX.doc
2: Enter the password (They all have the same password)
3: Remove the password
4: Save the document without its password

Is this possible with VBA?

• ## CPI Calculation with variable CPI over multiple years

Re: CPI Calculation with variable CPI over multiple years

Quote from AlanSidman;729068

Let me know if this is the number you are looking for?

this looks good, ill test it against some manual calcs and let you know how i go!!!

thanks for your help!

• ## CPI Calculation with variable CPI over multiple years

Re: CPI Calculation with variable CPI over multiple years

Quote from AlanSidman;729004

Understand. Look at the updated file.

This is looking really good, but it is calculating CPI assuming that the rental start period is 1972.

What is needs to do is the "Select Period" cell is the date the rent starts, so if the rent "Select Period" is Sept-1998, it should calculate CPI from sept-1998 through to current day.

I notice that if i put in the start period as mar-2014 with \$100 rent the current value is calculated at \$171.99 which is incorrect as it should only have increased by the CPI in Jun-2014 which was 0.006% so the resulting answer should be \$100.6

is there any way to do this?

thanks

• ## CPI Calculation with variable CPI over multiple years

Re: CPI Calculation with variable CPI over multiple years

Quote from AlanSidman;729004

Understand. Look at the updated file.

thats epic thanks!! it seems so simple now!

• ## CPI Calculation with variable CPI over multiple years

Re: CPI Calculation with variable CPI over multiple years

Quote from AlanSidman;728931

Look at the attached. Is this what you are looking for?

Those calcs look perfect, thats what I had initially, I have updated the sheet to include a table.

I dont think i was very clear in my initial post, so apologies for that, that i need is to be able to select the month/year from the table, then enter the \$ amount and have the current rate in the next cell.

Please can you see the updated sheet attached.

thanks so much for your reply and help.

• ## CPI Calculation with variable CPI over multiple years

Hi all,

I am trying to calculate rental CPI over multiple years with variable CPI.

I have attached the data i have acquired from the Australian Bureau of Statistics.

What i need to do is, for example, if the rent was \$100 in 1986, what would it be today.

Thanks in advance for help

• ## index and match help

Re: index and match help

thanks very much, very clear