+ Reply to Thread
Results 1 to 8 of 8

Price Variance and Dollar Variance for multiple stores on certain products

  1. #1
    Registered User
    Join Date
    06-18-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Price Variance and Dollar Variance for multiple stores on certain products

    I am working on another variance if statement. It seems I can not get this one the way I initially intedended. Is there anyone out there that can help with this?
    I am trying to do a comparison across the 4 stores for any at all price variance. I have 4 stores that list 4 values. In a perfect world the price would be equal but of course it isn't. You can see what I am talking about on the attached file. I am trying to find out which store has a variance and if so what the $ amount is. I have some of the if an statement but I dont think I have it down completely as I am asking for help. Please see the attached file.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    Can you provide what values you would want calculated in columns G and H?
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    06-18-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    Sample of values wanted
    Store Price Variances Store Price
    Item # Desc Store 1 Store 2 Store 3 Store 4 Variance Variance
    001038 Bread $2.96 $3.51 $3.51 $3.51 Variance in 1 $0.55
    001060 Water $0.59 $0.89 $0.59 $0.59 Variance in 2 $0.30
    001089 Milk $4.19 $- $- $4.19 Variance in 2&3 $4.19
    001091 Soda $0.99 $0.89 $0.99 $0.99 Variance in 1&2 $0.10
    001103 Tenderloin$3.49 $3.49 $3.99 $3.99 Variance in 3&4 $0.50
    001127 Chicken $2.89 $2.89 $2.89 Variance in 3 $2.89
    001141 Fruits $3.50 $3.50 $3.50 $3.50 No Variance $-
    001149 Veggies $2.80 $2.80 $2.21 $2.80 Variance in 3 $0.59

  4. #4
    Registered User
    Join Date
    06-18-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    Here it is attached with expected results.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    Would the attached work?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-18-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    I was hoping for a one column variance explanation. Plus if you take away Row 7, f7 cell you lose the three variances and it just says row 1 varoance when there should be a price for every store. I hope this helps. I cant get it on my end and its driving me crazy.

  7. #7
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    You should be able to search online for a formula to combine those formulas into one cell. I am not sure of how to do that without having too long of a formula, and multiple nested if statements.
    To fix the second issue, replace the formula in H5 with the following, and drag down/across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-18-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Price Variance and Dollar Variance for multiple stores on certain products

    That works for the 4 cells h-k and I appreciate it. I am going to leave this thread open to see if someone can ponder up away to do the same put in one nested cell. I appreciate all your help on this Melvin Robb.

+ 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