As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel Help

finnithfinnith ...TorontoRegistered User regular
edited August 2011 in Help / Advice Forum
Hey H/A,

In the past I've seen a lot of Excel experts on this forum so I'm hoping you guys will be able to help me with some Excel stuff.

So in one spreadsheet, I have a set of data organized horizontally. However, I need that data to be organized vertically in another sheet. I've experimented with using arrays, but since you can't change parts of arrays that formula gives me a lot of issues.

Another thing I want to learn about is the use of IF formulas. How do nested IF formulas work? I want to do something like and "If, then" statement. I'm trying to track merchandise sales, but we sell these customizable bundles that are giving me a lot of trouble. I'd like to set up the spreadsheet so that if all the items in the bundle are entered in the spreadsheet recognizes it as a bundle sale and uses the appropriate price. I'm not even sure this is the best solution because there are deluxe versions of certain bundles that include additional items. If I use IF statements it's possible that Excel will recognize it as a sale of the both the regular and deluxe versions of the bundle.

Any input? I apologize if this is asking you to do my work, but I do need some help with this.

Bnet: CavilatRest#1874
Steam: CavilatRest
finnith on

Posts

  • redfenixredfenix Aka'd as rfix Registered User regular
    Copy
    paste special
    transpose

    also, you probably need either =if(and( or =if(or(
    for nested ifs

  • redfenixredfenix Aka'd as rfix Registered User regular
    i'm tired but can elaborate tomorrow

  • finnithfinnith ... TorontoRegistered User regular
    So I think I fixed my second issue. I figured out how nested IF formulas work using mrexcel.com's message boards and just added in some conditions that makes sure that the regular and deluxe versions are recognized properly. My hope is that no one orders a bundle and then orders a shirt that would normally be included in the bundle.

    However I'm still having issues figuring out how to solve my first issue.

    Bnet: CavilatRest#1874
    Steam: CavilatRest
  • BlochWaveBlochWave Registered User regular
    The guy's advice should work for pasting a transpose. I don't remember where it is in older versions, but on 2007 and 2010 Excel I think you can just right-click, paste special, and check the transpose box. If you have a table of data, it transposes it so your rows are columns and your columns are rows. Is that not what you need?

  • DaenrisDaenris Registered User regular
    As redfenix suggested, if you have a horizontal section of data, and you select it, copy, then move to the new location you want to put it, right click, choose Paste Special, and check Transpose, it will flip your horizontal data to be vertical.

    For example, if you have:
    1 2 3 4 5 6
    and you do copy, paste special, transpose it will be:
    1
    2
    3
    4
    5
    6

    And if you have something like:
    1 2 3
    4 5 6
    and do a transpose, it will be:
    1 4
    2 5
    3 6

  • finnithfinnith ... TorontoRegistered User regular
    Sorry I forgot to address that solution. There's no equation that would do that automatically huh? I think I may just manually link them with simple "=cellreference" equations as orders come in then.

    Bnet: CavilatRest#1874
    Steam: CavilatRest
  • DaenrisDaenris Registered User regular
    I've never used the function version, but there is a transpose() function where you give it a range. This page details it well:
    http://www.techonthenet.com/excel/formulas/transpose.php

  • finnithfinnith ... TorontoRegistered User regular
    Yeah like I said in my OP I played around with the transpose function but I found it lacking since you can change parts of an array. It doesn't matter though, now that I have the spreadsheet set up I should be able to just use simple cell references to do that. Our daily sales volume is low enough right now that it's a feasible solution.

    Bnet: CavilatRest#1874
    Steam: CavilatRest
Sign In or Register to comment.