Excel: Getting the next value in a table rather than the first match

I recently needed to get the next biggest value in a table of values, and after surfing the net and looking at a few examples that where a little confusing this is how it is done:

• Use VLookup(value, TableRange, 1, TRUE) - true means approximate value, which means that in a sorted table it will choose the value equal or less than
• Then using Match(theVLookupAbove, TableRange) - This will return the row number of the value
• Then take the result from that use Index( TableRange, theMatchAbove + 1)

This will give you the result you are looking for.

Example:

2^N		VALUE	100
1
2		Formula	Value	Formula in Text
4		VLOOKUP	64	=VLOOKUP(D1,A2:A10,1,TRUE)
8		MATCH	7	=MATCH(D4,A2:A10)
16		INDEX	128	=INDEX(A2:A10,D5+1)
32		AllInOne	128	=INDEX(A2:A10,MATCH(VLOOKUP(D1,A2:A10,1,TRUE),A2:A10)+1)
64
128
256

A Quick Humus

As part of the Falafel recipe already listed, I never listed a Humus recipe, or more like how to make it.

Ingredients

Please note that my kids are very salt and pepper sensitive so adjust to taste.

• A tin of chickpeas, NOT drained
• 4 tablespoons of tahina (sesame paste)
• 2 cloves garlic, bottom cut off and pealed
• teaspoon of powdered cumin
• 3 or 4 tablespoons of lemon juice
• 2 or 3 tablespoons of olive oil
• salt and pepper to taste

Method

The one thing about Humus is it must be smooth. SO use a strong blender.

Empty tin into the blender then add garlic, tahina, the garlic cloves and cumin. add the 3 tablespoons of lemon juice and 2 tablespoons. And blend. Not that I have kept on standby the lemon juice and Olive oil. This is because you may need to add it later when the blender is struggling.

Blend or pulse until mixed, then taste, add lemon juice, salt and pepper to taste.

Then blend again.

If it is too thick add the extra olive oil

You can also add powdered coriander or paprika for taste,

You can use this with the Falafel recipe or even on a pita with a salad, yummy ;)

RPC over HTTPS does not work anymore- FIXED!

Recently I have had a problem with RPC over HTTPS. It has worked for years, but suddenly users told me it was not working. If they had told me when I would perhaps have found it quicker but instead, I had to wade through all the documentation and goodies.

For I tried to re set it up, that did not work. I tried reinstalling and that did not work.

Eventually I founds this cool tool online Test Exchange Connectivity and when I ran this test, and expanded the errors I was finally pointed in the correct direction.

In the IIS config of RPC. I click properties, and I saw that "Accept client certificates" was ticked. I unticked it and just like that it worked.

By the was cool trick I learnt to check the connectivity was to Ctrl+right click on the Outlook Icon in the right of the tool bar and the select Connection Status...

It then tells you TCP/IP or RPC.

Open letter to anti piracy movement

I am a big believer that you should support the people responsible for art and music by buying their product. I believe without support artists are not rewarded for their work.

The problem I have is with the distributors, and why I believe that electronic distribution is the only way forward. In South Africa it is unfortunate that this option is not really available, but the methodology I have adopted is to get items electronically then order the hard copy via an on-line shop or similar sometimes not even opening the package, when delivered, although normally I like to read the printed material out of interest.

Why do I prefer this. Well after I have purchased a product, I have to be subjected to the distributors advertising. I did not ask for it and why do I have to have it thrust upon me. Recently I order a few CD's I had downloaded and inside there was a advertising glossy, that is pretty easy to dispose of. But this is not the case with DVD's. Why do I have to be forced to watch advertising before I am allowed into the main menu of the DVD. This is ridiculous.

You cannot press menu to by pass, you are not given any option besides being forced to watch the advertising. If I download a feature from a site that allows me to, on the condition that I have the original media then I do not get adverts, I just get the feature. And besides bandwidth I technically did not pay for it.

For kiddies movies this is especially the case. Luckily the kids are not so fussy about 5.1 sound, so I have no purchased software that takes the feature film off the purchased DVD and lets me save it to another DVD, and I can even put a number on a single DVD. NO Advertising, no hassle auto play, the way it should be from the distributors.

So artists! Stop whining about piracy get the distributors to embrace the new (actually it is over a decade old) technology, or do it yourself.

While some artists and distributors are letting their coffee get cold, whining over lost revenue there are those artists that are getting rewarded by embracing electronic distribution. Give your fans easy access to the products they want and you will be rewarded.

Very useful note about baking

I found this online at http://www.prestoflour.com/Portals/TheArtOfBaking/portal.aspx?tabid=21#FAQ14

Assuming you are working with a well-balanced formula, when a cake falls, peaks and cracks, or has poor texture, the first thing to consider is the oven temperature. The next is the proportion of ingredients (how they are weighed or measured). Incorrect substitutions without adjustments are often the problem; jumbo eggs instead of large, all purpose flour instead of cake flour without making necessary adjustment, old baking powder.

Assuming you are following directions precisely and has accurately weighed or measured the ingredients, the most common cake problems and causes are:

Burnt on bottom with undercooked batter
Cause:

• Inadequate air circulation in oven

Burns on sides
Cause:

• Uneven heat in oven
• Oven too full
• Oven too hot
• Baked too near sides of oven

Coarse grain
Cause:

• Too much leavening
• Insufficient creaming
• Use of liquid shortening
• Use of ordinary flour
• Oven temperature too low
• Batter under mixed
• Too much baking powder

Cracked or peaked cake
Cause:

• Oven too hot
• Batter over mixed

Crust is cracked
Cause:

• Too much sugar
• Damp flour

Crust is sticky
Cause:

• Too much sugar
• Insufficient baking
• Damp flour

Crust is tough
Cause:

• Not enough shortening
• Not enough sugar
• Too much flour
• Oven too hot
• Cake baked too long

Crust too hard
Cause:

• Oven too hot
• Cake baked too long

Crust too light in color
Cause:

• Oven not hot enough
• Not enough sugar
• Not enough leavening
• Not enough shortening

Crust is soggy or doughy
Cause:

• Cake let steam while cooling

Does not rise, compact texture
Cause:

• Old or too little baking powder
• Cold eggs
• Cold butter

Dry
Cause:

• Too much flour
• Too much leavening
• Not enough shortening
• Not enough liquid
• Not enough sugar
• Over beaten egg whites
• Over baked or baked too long or at too low temperature
• Pan too big

Falls
Cause:

• Too much shortening
• Too much baking powder or soda over leavens mixture
• Too much sugar
• Batter under mixed
• Insufficient baking
• Oven temperature too low

Falls apart when taken out of pan
Cause:

• Too much shortening
• Too much leavening
• Too much sugar

Heavy and compact
Cause:

• Over beating batter
• Too much shortening
• Not enough sugar
• Too much liquid
• Under beaten yolks
• Incorrect baking temperature

Humps or cracks on top
Cause:

• Oven too hot at first
• Too much flour
• Not enough liquid

Rises higher on one side than the other
Cause:

• Uneven heat in oven
• Oven grate not level
• Batter spread unevenly in pan

Runs over top of pan
Cause:

• Too much batter in pan
• Oven not hot enough
• Too much leavening
• Too much shortening or sugar

Soggy layer or streak at bottom
Cause:

• Under mixing of ingredients
• Shortening too soft
• Not enough leavening
• Lower part of oven not hot enough
• Under beaten yolks
• Too much liquid
• Damp flour

Sticks to pan
Cause:

• Pan not properly prepared
• Cake left in pan too long
• Too much sugar

Too light, crumbly and dry
Cause:

• Too much leavening

Tough
Cause:

• Not enough shortening
• Not enough sugar
• Over beating batter
• Oven too hot

Undersized
Cause:

• Not enough baking powder or soda to leaven the mixture.
• Mixture baked in too large a pan
• Oven too hot

Things I learn today, cause of lesson, cooking cup cakes for my daughters birthday ring. Next time I will have a decent cup of coffee and then start. I think I may have over measured something...

Where there is a will…

The eagles have really dealt with this before, in their song "get over it". I am amazed that every day there are people that blame something for the reason they have not succeed.


However looking around you see many, many people that have achieved a lot more even though they have started with less than those who are very good at pointing fingers.


In fact almost every self-made successful person had to fight adversity to get to where they got. I seldom hear a successful person blame other people for things, they normally get their nose down and graft through anything thrown in their way.


What has driven this sudden entry, well I meet Michaela Mycroft in 2006, just as The Chaeli Campaign was getting going, and this week she won an international peace prize and she is only 17. Many for more abled bodied people should look at her and see that it is the mind and the determination of spirit that is important, rather than concentrating on the whys and why nots.


Congratulation Michaela!

Chaeli wins International Childrens Peace Prize 2011

 

Julia and Lucy’s Easy Tomato and Lentil Pasta

I am teaching my 7 year old daughter (Julia) simple meals to cook, so that she can cook her own meals. While she still requires a little help, and Lucy almost 4 years old always gets a tasks this was an easy recipe and it really did taste moreish.

Ingredients

Yummy

• Tin of peeled whole tomatoes (or you can peel 4 tomatoes, if you do not want the sugar that comes with the tinned ones)
• A Tin of Lentils (or about ½ a cup soaked overnight in 1 cup of water and a teaspoon of vinegar)
• Dried powdered Cumin and dried Basil
• One chopped onion
• About a teaspoon of crushed garlic
• A handful of fresh basil
• Pasta, we used spinach Tagliatelle (made using spinach juice)
• 2 x teaspoons olive oil
• Optional ¼ cup red wine

Method

1. Take medium size saucepan and place on stove, get it warm (should take between 1 and 2 mins)
2. Add oil, add onions, and cover the onions with oil
3. This must cook for about 3 mins
4. While that is busy, blend the tomatoes and lentils, and a teaspoon of cumin, until smooth’ish, not too much (for tins drain the liquid, not completely)
5. That should not take more than 3 mins.
6. The onions should be getting clear, no add the garlic and basil and stir that in, simmer for 1mins add red wine cook off for about 1 min
7. Then add the blended mixture
8. Simmer for about 15-20 mins (until most of liquid gone)
9. In the meantime chop the basil
10. With 5 minutes to go, add boiling water into pot deep enough to cook the pasta (a decent amount) add 1 table spoon salt
11. Then add the pasta into the pot and turn off and cover the sauce
12. The pasta we used need 4 mins to cook, once time is up drain in colander and place back over the pasta pot and sprinkle with olive oil
13. Add chopped basil into the sauce, and stir it in
14. Serve (with some grated Parmesan if required)

enjoy...

TILT - more

Things I learnt today

the web.config file is the bees knees. It tells the website all the things it needs to know, where to find the extensions what to do with what. I works like other html tags with "<" and ">" and "</" and ">"


See - 

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=307 

http://www.dotnetrobert.com/dotnet/DotNetBlog/tabid/83/EntryID/6/Default.aspx

to get some guidance, and believe you need it, knowing what the file does and knowing how are two different things.

I am still not clear on it.

One thing I can say is the version numbers for DLLS must match the version numbers on the DLL

Also learnt what AJAX is

Ajax (sometimes called Asynchronous JavaScript and XML) is a way of programming for the Web that gets rid of the hourglass. Data, content, and design are merged together into a seamless whole. When your customer clicks on something on an Ajax driven application, there is very little lag time.

http://webdesign.about.com/od/ajax/a/aa101705.htm

Some things I have learnt, that are Easy Peasy, about databases and SQL Express

The is real 101 stuff, but though I would blog it anyway.

To create a new database is SQL Express:

• Run SQL Server Management Studio (if you do not have it download it from Microsoft downloads)
• Expand the connected database engine (if you are not connect to one then connect)
• Then right click on the Databases folder
• Click New database…
• Then fill in a database name and ok
• There is a new empty database.

The data type to use for a new table in SQL express that is autoinc

• Create a new table
• Type in the column name
• Select datatype int
• In Column Properties click the [+] next to Identity Specification
• Next to (Is Identity) select Yes
• If you want to make that the primary key right click on the row and select Set Primary Key

Oh and when you create a table it asks you to name it when you save it.

Recovering SBS 2003 after hardware failure - WOW!

Yesterday afternoon after 6 years of solid uninterrupted service my hard drive on my SBS 2003 server failed.

I was pissed, the server had been telling me for about 3 months that it was coming, and I had just ordered a drive to mirror the system volume, and it had failed 2 days too early. My BAD.

After calming down I thought well I better pop out and get a new SATA drive (or 2, to do the mirror). I had been backing up; using standard NTBackup, to an external drive (which I was alternating) and the backup has been reporting success. I also use SyncToy to backup the critical data to an external drive attached to my Laptop, so I was confident the critical data could be recovered. I saw a mountain ahead to climb as I was a bit worried about all the settings for:

• users
  
• firewall,
  
• RRAS,
  
• VPN,
  
• SSL certificate for Active Sync access
  
• mailbox data on Exchange
  
• my SQL Express data and
  
• the internal websites
  

Once I had purchase a drive, I started loading the SBS box again. I googled to see if there were any white papers and found this one: http://download.microsoft.com/download/b/d/8/bd8e1a40-d202-429a-8eb7-26300d62bcc9/bku_bkuprstr.doc, which I used as a guide.

 

So what was the process, and it is this simple:

• Install SBS2003, but stop once it gets into Windows (see doc)
  
• Go to windows update, and get the latest service packs and updates (this took 4 hrs.)
  
• Once that is done, restore (I have used the standard backup using the scheduler) everything but NOT the Information store. Basically data, and system state
  
• Once that is finished, reboot
  
• AND IT IS all back to normal, WTF I was blown out of my chair.
  

Everything that was MS based worked. The only problem I had was with QuickBooks, it would not run. Repair did not I had to uninstall and delete the program files, including the license data under appdata, and then install run QuickBooks update and then people could access QuickBooks on the server, but the server QuickBooks did not run. Will try a reboot to see if that fixes it.

So 14 hours and about 12 cups of coffee later, we were up and on brand new drive! QuickBooks took 2 hours of that.

 

Well done Microsoft!