+ Reply to Thread
Results 1 to 2 of 2

Actual as a % of Budget formula

  1. #1
    Registered User
    Join Date
    02-13-2012
    Location
    Covington, LA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Actual as a % of Budget formula

    A problem has arisen with this spreadsheet I do monthly analyzing net income 30 branch locations for my company. I need a formula that will calculate actual net income as a % of budget at each location. Obviously, when both numbers are positive, this is simply 'actual/budget'. But I need something that will calculate the correct % regardless of positive or negative values.

    For example, 1 branch has an actual net income of 10,000 and a budget of 8,000; another branch has an actual of (2,000) and a budget of 5,000 while yet another branch has an actual of (15,000) and a budget of (10,000). My problem arises because the 'actual/budget' for the second and third is not accurate since it creates a negative % or a % over 100% respectively. Is there a formula that could accurately calculate the percentage of budget for all three of these without having to modify anything? I'm thinking of a complex "if" statement, but I can't figure out how to put it. Thanks for any input.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Actual as a % of Budget formula

    What would you expect the % should be?

    With A1: actual; B1: budget

    Case 1: (1)= 125%; (2)=0%; (3) = 0%
    ------------Try this: =IF(A1>0,A1/B1,0)

    Case 2: (1)= 125%; (2)=(140%); (3) = (250%)
    ------------Try this: =IF(A1>0,A1/B1,(A1-B1)/B1)

    For myself, I refer to case 2, which help me to see how black the picture is!
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1