Search results by user ID 2102

UK 2024/25 Excel formulas to work out take home, NI and income tax.

doverton — Thu, 29 Aug 2024 09:55:37 GMT

I know most people don't need this sort of information, but since I just entered it into a spreadsheet and found the information wrong in Co-Pilot, I thought I'd share the correct information.

In the UK, NI rates can be found here and income tax rates can be found here.

The formula to calculate NI is as follows. I've used the LET function to make it easier to read. This presumes the annual salary is in cell A1. If you want a monthly figure, you will need to divide PT and UEL by 12 (directly in the formula eg “12570/12”)


=LET(
    salary, A1,
    PT, 12570,
    UEL, 50270,
    rate1, 0.08,
    rate2, 0.02,
    IF(salary <= PT, 0,
        IF(salary <= UEL, (salary - PT) * rate1,
            (UEL - PT) * rate1 + (salary - UEL) * rate2
        )
    )
)

The UK Tax calculation would look like this. It has a lot of variables.


=LET(
    salary, A1,
    pension_rate, 5%,  
    pension_contrib, salary * pension_rate,
    adjusted_salary, salary - pension_contrib,
    base_PA, 12570,
    BR, 50270,
    HR, 125140,
    rate_basic, 20%,
    rate_higher, 40%,
    rate_additional, 45%,
    reduced_PA, MAX(0, base_PA - MAX(0, (adjusted_salary - 100000) / 2)),
    taxable_income, MAX(0, adjusted_salary - reduced_PA),
    basic_tax, MIN(taxable_income, BR - reduced_PA) * rate_basic,
    higher_tax, MAX(0, MIN(taxable_income - (BR - reduced_PA), HR - BR) * rate_higher),
    additional_tax, MAX(0, taxable_income - HR) * rate_additional,
    basic_tax + higher_tax + additional_tax
)

I hope these are of interest if you needed them.

To calculate your take home, it is salary – NI – tax – salary*pension_rate, which would look like this if all entered into a single formula, although you could replace the whole of the NI and tax calculations with a cell reference if you already have used the formulas above.


=LET(
    salary, A1,
    pension_rate, 5%,

    tax, LET(
        pension_contrib, salary * pension_rate,
        adjusted_salary, salary - pension_contrib,
        base_PA, 12570,
        BR, 50270,
        HR, 125140,
        rate_basic, 20%,
        rate_higher, 40%,
        rate_additional, 45%,
        reduced_PA, MAX(0, base_PA - MAX(0, (adjusted_salary - 100000) / 2)),
        taxable_income, MAX(0, adjusted_salary - reduced_PA),
        basic_tax, MIN(taxable_income, BR - reduced_PA) * rate_basic,
        higher_tax, MAX(0, MIN(taxable_income - (BR - reduced_PA), HR - BR) * rate_higher),
        additional_tax, MAX(0, taxable_income - HR) * rate_additional,
        basic_tax + higher_tax + additional_tax
    ),

    ni, LET(
        PT, 12570,
        UEL, 50270,
        rate1, 8%,
        rate2, 2%,
        IF(salary <= PT, 0,
            IF(salary <= UEL, (salary - PT) * rate1,
                (UEL - PT) * rate1 + (salary - UEL) * rate2
            )
        )
    ),

    take_home, salary - ni - tax - salary * pension_rate,
    take_home
)

Webinar: Overcoming the challenges of a Dispersed Workforce Apps, Data and Security

doverton — Wed, 05 Aug 2020 08:24:00 GMT

I'm delighted to be presenting with two other gentlemen from the British Computer Society on delivering solutions for remote working during the Covid Pandemic.

The speakers will be:

Paul Richards
Dr John McCarthy PHD B.S.C (HONS) MBCS
David Overton BSc

Agenda

18:00 - Welcome, Philip Crewe
18:05 - Introduction to Challenges Working and Security, Shakeeb Niazi
18:10 - What are the challenges that organisations like the NHS are facing, Paul Richards
18:30 - Demo of cyber challenges that remote working has exposed, Dr. John McCarthy
18:50 - How new products and services have been created to solve some of the key issues. An NHS success story, David Overton
19:10 - Q & A – Panel Session - Moderated by Shakeeb Niazi
19:30 - Close

To register for the event please go to https://www.bcs.org/events/2020/august/webinar-overcoming-the-challenges-of-a-dispersed-workforce-apps-data-and-security/

Citrix Essentials seems to be edging out the partners whilst Awingu is welcoming them

doverton — Thu, 09 Feb 2017 11:00:00 GMT

Azure RemoteApp was the official Microsoft way to run your Windows Desktop Applications on Azure and access them from any device. The service was only available for a short time before Microsoft closed the solution stating that you would need a partner’s software to complete a solution.

Awingu is one such partner, offering the ability to access your Windows applications via any html browser on any device without the need for plug-ins or downloads, while ensuring a highly secure solution.

Awingu offers so much more than this though as Awingu is a Workspace Aggregator. We offer Single Sign On to all your cloud applications and access to all your files, whether on internal file shares or stored in cloud solutions. This is wrapped in security while enabling easy collaboration. Have a look at http://www.awingu.com/awingu-vs-azure-remoteapp to see how much more Awingu offers.

We expect our partners to implement and manage the solution and if they want, our partners can deliver this as a multi-tenant service to their customers as Awingu offers this natively out the box. Today, you can through the Azure Marketplace, deploy the Awingu All in One solution and be deployed rapidly, waiting for you to add your applications and users/customers.

Microsoft also announced that one way to move on from Azure RemoteApp was to lean on their previous relationship with Citrix. End of life for Azure RemoteApp was announced on August 12^th 2016 and the end of support is August 31st 2017. Citrix have stated they will deliver their Essentials solution in the first half of 2017. It’s looking like it will be the later part of the half year, leaving customers and partners have very little time to migrate their users to a new service if they wait for Citrix. With Awingu, you can start today.

Listening to the Citrix webinar several problems leapt out at me for both customers and partners.

You must pay Citrix to run their cloud, rather than being able to host it yourself on Azure, charging your customers for the solution and making margin on this service
Citrix is recommending that customers can and should manage their Application infrastructure without partners
You need to take several Citrix products just to get up and running. At the very least you need Netscaler, XenApp, XenMobile and ShareFile to offer the functionality that Awingu offers
Citrix Cloud requires products to be installed onto key Windows infrastructure, such as the Domain Controller
Citrix upgrades the backend on their schedule, so no customer choice in the timing of your upgrades
The Citrix cloud and their traditional on-premises products cost significantly more than an Awingu solution, both in licenses and running costs.
Finally, it is still not available to implement yet!

Where do partners sit?

At Awingu, we only supply Awingu through partners. Whilst Awingu is simple to install and configure, we believe that customers want to manage their business and that our expert partners offer significant value in ensuring that the applications and files the customer needs available. With Citrix Essentials, the role of the partner is being reduced meaning that the opportunity to add value, and being rewarded for it, is also reduced. From the same webinar that launched their intention to deliver Citrix XenApp Essentials this year, Citrix seemed to suggest that partners are only required to provide a hosted, multi-tenant solution. You can see this in a from one of the slides they presented. At Awingu we know that partners add much more value in delivering and integrating a solution for customers, especially when combined with the newer cloud applications that customers want to use.

Comparing the two solutions

Let’s compare the differences between Citrix’s announced services and the solution available from Awingu today. The Awingu solution will run on any cloud or on-premises without any change of solution. You can see it below. That is the sum total of all the components and the use of SQL is optional. We do not install any technology on your Microsoft servers, but simple integrate to them using the standard Microsoft protocols. Awingu can easily be integrated into your existing infrastructure without disrupting it at all.

By comparison, the diagram below is the Citrix Cloud solution, the only way to get the Citrix XenApp Express solution as described by Citrix in their recent webinar to partners. Not only is it significantly more complex, but you must pay for Citrix to run their cloud services for you, whether you want all the products they put in the bundle or not.

I’ve highlighted the places where you must install Citrix software by using blue boxes. You can see that you must install Citrix components on all your existing infrastructure to start using Citrix. The Citrix Reference Architecture also states that “Also, the Active Directory server you are using must have Citrix Group Policy Management installed.” Personally, I find the installation of the Citrix solution very intrusive. With Awingu, the impact on your other systems is simply network access, without the need to install Awingu components elsewhere.

Now, because you don’t have control of the Citrix Cloud, as that software is updated by Citrix, you have no choice but to match their schedule in the upgrades you perform on your own platforms running the applications.

Citrix comparison to Awingu

As I mentioned, Citrix is significantly costlier to purchase and maintain than Awingu. You can see more on the TCO comparison on the site Migrate2Awingu.

When it comes to what is in the box, Awingu gives you all the functionality that you need, going beyond the traditional Citrix solutions. Not only does Awingu offer the remoting of applications, but also, using SSO we enable access from the same interface to your cloud applications and all your files, while offering a secure, auditable, collaborative solution.

Using the information that is available from the Migrate2Awingu site, you can see how the Awingu product lines up against Citrix

With all those different components, upgrading can be very complex with Citrix. I remember reading about the process which is well documented on Terence Luk’s blog. Awingu upgrades are so very different. If you watch this 1 minute video, you can see the Awingu upgrade process. Upgrading though 4 versions took much less than an hour and was about 12 mouse clicks.

Moving forward with Awingu

There are several ways to move forward with Awingu. If you want to learn more about Awingu, please visit Awingu.com or request more information or sign up for a demo account please go to our Contact Us page.

If you would like to become a partner, please share your information on the "Become a reseller" page.

You can run Awingu on-premises or on any cloud, however if you want to learn more about the Awingu All-in-One solution available on Azure today, please look at Awingu on the Azure Marketplace.

For more information about putting Awingu into your existing Citrix infrastructure to test and eventually migrate away from Citrix, have a look at this page.

How to Use Office Conditional Formatting to put in icon sets comparing a range of cells, or relative references as Office calls it

doverton — Sat, 04 Feb 2017 00:56:00 GMT

So it has been a while since I last dropped out a blog, but this one was useful to me at work, so I thought I would share.

Office 2016 (or 2013 and 365) has some lovely functions, one of them being conditional formatting. I use this quite a lot, but one thing stumped me. Below is what I wanted to get to:

When I wanted to compare to tables of data and put in place icons to show if the data had moved up or down, I could not do it. Excel would politely tell me that “This type of reference cannot be used in a Conditional Formatting formula”

Now, not being one to like these limitations, I realised that I could do it cell by cell, but that would be an awful lots of mouse clicks, so I’ve written this short piece of VBA to do it for you. To open the VBA environment, press-Alt-F11 then right click your VBAProject and select Insert Module. You can then paste the code from below into that window and modify the CompareIcons sub to match your needs. The “CompareIcons” sub makes a call GenericIconsComparison which implements the conditional formatting. The parameters are:

Parameter	Description of use
IconsTopleft As Range,	This is the location of the top left corner of the cells where your icons will appear. It can also be the complete range of cells that you want to put in comparison icons
CompareTopLeft As Range,	This is the top left location of the cells that you are comparing to
Rows As Integer,	If you only entered a single cell for the IconsTopLeft parameter, then you can enter the number of rows here
Cols As Integer,	If you only entered a single cell for the IconsTopLeft parameter, then you can enter the number of Columns here
Icons As XlIconSet,	Within Excel there are a number of icons, I used xl3Arrows
ReverseOrder As Boolean,	If you wish to make it that higher values are red and lower valued are green, put True here, otherwise put in False
ShowIconsOnly As Boolean,	If you want to hide the values in the cells and only show icons, put True here, otherwise put False
RemoveOtherCondFormatting As Boolean	If you want to remove any other Conditional Formatting, including previous use of this command, set this to True

The code – first a sample macro to call the routine to deliver the comparison icons

Sub CompareIcons()

'In this example it starts in cell Q202 and compares to the cell Q210 and does this for 5 rows and 9 columns. The icons used at xl3Arrows and removes all other conditional formatting on the cells impacted.

Call GenericIconComparison(Range("q202"), Range("q210"), 5, 9, xl3Arrows, False, False, True)

'In this example it compares the range from Q202:Y206 to the cells starting in Q210, so in effect the same as the one above

Call GenericIconComparison(Range("q202:Y206"), Range("q210"), 0, 0, xl3Arrows, False, False, True)

'In this example it does the same as the others, except higher values get a downward arrow and lover values get a higher value

Call GenericIconComparison(Range("q210"), Range("q202"), 5, 9, xl3Arrows, True, False, True)

End Sub

Below is the VBA code to implement everything I’ve spoken about. It does what is says on the tin.

Sub GenericIconComparison(IconsTopleft As Range, CompareTopLeft As Range, Rows As Integer, Cols As Integer, Icons As XlIconSet, ReverseOrder As Boolean, ShowIconsOnly As Boolean, RemoveOtherCondFormatting As Boolean)
'
' Icon Comparisons for ranges
'

    'get column top left for from and too ranges
    from_col_number = IconsTopleft.Column
    to_col_number = CompareTopLeft.Column
    'If a range is given, use that over rows and cols parameters
    If IconsTopleft.Columns.Count > 1 Then Cols = IconsTopleft.Columns.Count
    If IconsTopleft.Rows.Count > 1 Then Rows = IconsTopleft.Rows.Count
    For i = 1 To Cols
        'get Column letter for from cell
        from_col = from_col_number + i - 1
        If from_col > 26 Then
            col = Chr(64 + Int((from_col - 1) / 26)) + Chr(64 + from_col - Int((from_col - 1) / 26) * 26)
        Else
            col = Chr(64 + from_col)
        End If
        'get Column letter for comparison cell
        to_col = to_col_number + i - 1
        If to_col > 26 Then
            ToCol = Chr(64 + Int((to_col - 1) / 26)) + Chr(64 + to_col - Int((to_col - 1) / 26) * 26)
        Else
            ToCol = Chr(64 + to_col)
        End If

        'create the rules
        For j = 1 To Rows
            'select the cell
            Range(col + Trim(j + IconsTopleft.Row - 1)).Select
            'clear other formatting if desired
            If RemoveOtherCondFormatting = True Then Selection.FormatConditions.Delete
            'add the rule to compare to other cell
            Selection.FormatConditions.AddIconSetCondition
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1)
                .ReverseOrder = ReverseOrder
                .ShowIconOnly = ShowIconsOnly
                .IconSet = ActiveWorkbook.IconSets(Icons)
            End With
            With Selection.FormatConditions(1).IconCriteria(2)
                .Type = xlConditionValueNumber
                .Value = "=$" + ToCol + "$" + Trim(j + CompareTopLeft.Row - 1)
                .Operator = 7
            End With
            With Selection.FormatConditions(1).IconCriteria(3)
                .Type = xlConditionValueNumber
                .Value = "=$" + ToCol + "$" + Trim(j + CompareTopLeft.Row - 1)
                .Operator = 5
            End With
        Next
    Next
End Sub

blah

Fixing my Office 365 Lync Voice (P1) and PGi world wide phone integration problems–error ID 3110 (source ID 243)

doverton — Sun, 22 Sep 2013 14:39:06 GMT

I use Office 365. Probably no surprise for many, but I like the world wide access and simplicity. However, since leaving Microsoft I found having to Skype people a little frustrating if I wanted to host a multi-party conference call, especially if some people need to dial in using a phone, rather than Lync from a PC. To this end I ended up purchasing a certified solution from PGi.

PGi charge me per minute per person and I have dial in and out capability, so I can dial people to join the call and people can dial in with numbers for most countries around the world. Perfect, just like the solution I had when I worked at Microsoft.

Except it didn’t work. People could dial the conference number and hear each other and others could be connected via VoIP, but the two groups could not hear each other. Not the solution I wanted. I ran the DNS tests that Microsoft asks for and did test calls with the nice people from PGi, but nothing was showing up as the error. Searching the internet showed various potential solutions, but nothing seemed to be working. When I tried to dial people in, I got this error here:

Eventually, I came across a post that said it was a licensing problem and the users needed to be un-licensed, left for 15 minutes, then re-licensed. I tried this and hey-presto, it all worked. So here is the fix I used:

Go to http://office.microsoft.com and sign in with your office 365 credentials

Select users and groups from the admin screen

Select the user you want to enable and press the edit link that looks like a pencil

Now press licenses on the left hand side

Remove the tick from Lync Online (P1) and select save. Repeat this for each user that needs to have access to Lync Voice

Now, have a nice coffee, leave it at least 5 minutes, but 15 is advised

Now go back, do the same again for the users, but re-select Lync Online (P1) and save the changes for each user.

For me, I was able to now schedule a meeting and it all works – hopefully the same will work for you. If not, Microsoft will want you to check your DNS settings and other things, but for me at least, this was the solution.

Thanks

David

Re: stdole32.tlb error with excel 2007

doverton — Wed, 17 Apr 2013 08:30:54 GMT

Michael,

I just don't have an answer for the stdole32.tbl issue. Some people have suggested completely removing Office, it's files and reg entries and re-loading as a way to fix, but this has no guarantees. My fix simply fixes one issue. Also, changing DWORD to DEXCEL does not work as DWORD is an instruction to the REG command, not something that will impact office :-)

Thanks

David

Re: stdole32.tlb error with excel 2007

doverton — Tue, 16 Apr 2013 06:17:23 GMT

The links in this post where inappropriate and removed. However, the post for the solution talked about can be found here

Re: Downloading Script Bundle

doverton — Mon, 01 Apr 2013 17:05:04 GMT

Hi,

could you e-mail me at admin@davidoverton.com and I let me know which link is not working? It should be, so I will put it back.

Thanks

David

Version 7 now in store

doverton — Wed, 27 Mar 2013 21:04:52 GMT

The address is http://apps.microsoft.com/windows/en-GB/app/sync-for-skydrive/dba55c55-efdd-42f9-b249-69945c6fab5b, but if you have it already, the store update process should work for you.

Version 8 product plans

doverton — Wed, 27 Mar 2013 21:04:39 GMT

Hi,

as always, led by your feedback, the following are currently on the plan of record for release 8:

Initial scan to only process root folder of SKyDrive
Folders will start collapsed and you can expand / collapse via the normal [+] next to it. At this point the next folder level down will be scanned and displayed
I will try to add "search" functionality so you can see files that match a keyword
Speed of screen updates will be back on the agenda to be improved
Drag whole group of files - so not just selected files, but also a file group header (eg all updated files)
List view - view the files as a single list with the operation information in a row with the file name and folder name