Tag Archives: excel

Excel: Calculate Cost, Margin, Sell Price

Posted on by
Reply

KB ID 0001835

Problem

Occasionally at work I need to work out what the sale price / retail price will be for something would be (given that I know the cost and the % margin). That’s easy to work out. But sometimes I get given the sell / retail price and I know the margin used, and I then must work out what the cost will be.

Ordinarily if it’s a quick question I’ll just use and online margin calculator. But if I have a LOT of items to price – then Excel is the way to go.

Solution: Working out the Sell Price

If you know the cost and the margin to be applied, this is how to work out the retail price.

Solution: Working out the Cost Price

If you know the retail (Sell) price and the that was applied, this is how to work out the cost price.

Related Articles, References, Credits, or External Links

Special thanks to Mr Andrew Dorrian, who worked out the formula for the ‘cost price’ while I swore a lot!

Excel – IP Address Formula for ‘Auto fill’

Excel – Creating a Dropdown Box ‘From data on another sheet’

 

Exchange – Export Distribution Groups AND Members to a file

Posted on by

KB ID 0000209 

Problem

Yes you can use the Powershell commands,

Get-DistributionGroupMember –identity “group name” | ft name, primarysmtpaddress

But you have to do that for every group and I’ve tried Piping the Get-DistributionGroup in there but it does not seem to want to work 🙁

Solution

1. Download this script and extract it to the root of the Exchange servers C: drive.

2. On the Exchange server > Click > Start > All Programs > Microsoft Exchange Server 2007 > Exchange Management Shell.

3. Issue the following command cd “c:” {enter}

4. Issue the following command ./all_members.ps1 {enter}

5. On the Exchange Server navigate to C:Exchange_Groups.csv (open with Microsoft Excel).

Possible problem;

Powershell Scrpt signing Policy

Change scipt execution policy with a set execution command.

Depending on your script signing policy, you might see.

File {path} cannot be loaded the file {path} is not digitally signed. The script will not execute on the system. Please see “get-help about_signing” for more details..

If you see this enter the following,

Get-ExecutionPolicy {enter}

It will say Restricted, Remote Signed or All Signed “Take Note”

Issue the following command

Set-ExecutionPolicy Unrestricted {enter}

Then run the all_member script, when finished change it back with

Set-ExecutionPolicy {what it was earlier}{enter}

 

Related Articles, References, Credits, or External Links

All Credit to Jon-Alfred Smith – Who put the script here

Microsoft Excel – SGXLADDIN.dll could not be found

Posted on by

KB ID 0000505 

Problem

Seen when launching Microsoft Excel..

Error:
‘C:WindowsSysWOW64SGXLADDIN.dll’ could not be found, Check the spelling of the file name, and verify that the file location is correct.

Solution

You are seeing this because you have installed a Sage accounting product in the past and it dropped some add-ins into Excel.

1. Close the error > Click File> Options > Add-Ins > Select the “Sage Excel Integration” > Go.

2. Untick sgxladdin > OK > Yes.

3. Now restart Excel and you should be error free.

 

Related Articles, References, Credits, or External Links

NA

Excel – Creating a Dropdown Box ‘From data on another sheet’

Posted on by

KB ID 0000711 

Problem

My wife asked me how to do this today. I don’t use Excel much, most people think because I work in IT I know everything about all software packages, and they are surprised when I’m asked questions like this, and I say ‘I don’t know I’m a network engineer’.

But seeing as it was for the better half, I fired up Excel and worked it out. (This must be worth at least a cup of Tea!)

Solution

Step 1 – Create the source Data

1. Select the sheet that you want to populate the drop down list from, or create a new one and call it something sensible.

2. Type all your values into a column.

3. Select the column (you can select the cells but then if you add any new data it wont be added to the drop down).

4. In the ‘Name’ Section give the range a name i.e. garment, then press Enter.

Step 2 – Create the Drop Down List

1. Select the sheet that you want the drop down box to appear on.

2. Select the Cell where you want the drop down.

3. On the ‘ribbon’ select data.

4. Data Validation.

5. Settings tab.

6. Change allow to ‘list’.

7. Set Source to ‘={the range name you used above}’ OK.

8. The drop down (list box) will be created.

Related Articles, References, Credits, or External Links

Excel: Calculate Cost, Margin, Sell Price

Excel – IP Address Formula for ‘Auto fill’

Excel – IP Address Formula for ‘Auto fill’

Posted on by

KB ID 0000928 

Problem

I got asked this week if I know how to do this, a colleague was doing a spreadsheet, and needed to quickly put in IP addresses. The Problem is if you use ‘Auto Fill’ to continue your IP addresses, it does not behave quite like you would expect.

Solution

A quick search got me most of the way there, but the solution is different depending on which ‘octet’ of the IP address you want to increase.

So simply change the figures in red below, drop the formula into a cell on your spreadsheet then use the auto fill option.

Excel IP Addresses Increment the First Octet

[box]
=””&ROWS($A$1:A10)&”.1.1.0“[/box]

Excel IP Addresses Increment the Second Octet

[box] =”10.“&ROWS($A$1:A1)&”.1.1“[/box]

Excel IP Addresses Increment the Third Octet

[box] =”10.1.“&ROWS($A$1:A1)&”.1“[/box]

Excel IP Addresses Increment the Fourth Octet

[box] =”10.1.1.“&ROWS($A$1:A1)[/box]

 

 

Related Articles, References, Credits, or External Links

Excel: Calculate Cost, Margin, Sell Price

Excel – Creating a Dropdown Box ‘From data on another sheet’