Welcome Guest
Log In | Register )
You last visited January 21, 2017, 4:32 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Help

Topic closed. 28 replies. Last post 4 years ago by SergeM.

Page 2 of 2
PrintE-mailLink
Avatar
Pennsylvania
United States
Member #109275
April 9, 2011
1503 Posts
Offline
Posted: January 6, 2013, 4:00 pm - IP Logged

Thanks everybody for posting.

The VBA is a little light years ahead of me, so i started reading that book that somebody posted in learning VBA.

The author says the meat and potatoes of the VBA is in chapter 11.

The book has 900 pages, and I only just started reading and am in chapter 1, page 45, so I will see how this develops.

Right now my non VBA formula is analyzing one cell that counts the even occurences.

So my current formula is,

Well I started with this and then I got stuck.

=IF(DJ1488=2,"3O/2E",0)

However the following formula got an error value.

=IF(DJ1489=2,"3O/2E",0)+IF(DJ1489=3,"2O/3E",0)

so thats when I gave up.

It can be pretty involved. I have a college degree in programming and one of the things I learned was VB6, so its pretty easy to move it to VBA. Def read up the book and learn what you can. VBA makes Excel way more powerful that Excel already is.


    United States
    Member #124493
    March 14, 2012
    7023 Posts
    Offline
    Posted: January 6, 2013, 4:17 pm - IP Logged

    It can be pretty involved. I have a college degree in programming and one of the things I learned was VB6, so its pretty easy to move it to VBA. Def read up the book and learn what you can. VBA makes Excel way more powerful that Excel already is.

    Thumbs UpType

      Avatar

      United States
      Member #41846
      June 23, 2006
      460 Posts
      Offline
      Posted: January 6, 2013, 7:30 pm - IP Logged

      I'm glad you posed this question. I've learned something today. you in fact can combine text and formulas in the same cell.  in the cell you want your results,

      ="E "& sumproduct((mod(b1:f1,2)=0)+0)&" O "& sumproduct((mod(b1:f1,2)<>0)+0)

      b1:f1 is your data range adjust as needed.


        United States
        Member #124493
        March 14, 2012
        7023 Posts
        Offline
        Posted: January 6, 2013, 7:45 pm - IP Logged

        I'm glad you posed this question. I've learned something today. you in fact can combine text and formulas in the same cell.  in the cell you want your results,

        ="E "& sumproduct((mod(b1:f1,2)=0)+0)&" O "& sumproduct((mod(b1:f1,2)<>0)+0)

        b1:f1 is your data range adjust as needed.

        Thumbs Up

        Yeah my charts are spot on, it just I have to do some fine tuning.  Its one of my new years resolutions.

        I use a lot of countifs.

        There was a post that I made as a favorite, giving the answer to a question somebody posed.  Cant seem to find it though!

        But I found a lot of interesting excel stuff and weird vba stuff.

        For me i just fill in cells,  then the vba is done automatically, but you goes are doing the reverse. 

        There are some cool objects that I use that you can make transparent and it looks pretty fresh.

        Arrow, and circles, etc, numbers, its really a neat tool.


          United States
          Member #124493
          March 14, 2012
          7023 Posts
          Offline
          Posted: January 6, 2013, 7:49 pm - IP Logged

          My programmer provided these charts to me.

          I use them daily in every game i play.

          -100-100-4-1-1-1
          -2-1000-1-5-200
          -300-10-2-6-30-1
          -4000-1-30-40-2
          -5-100-200-50-3
          -60-10-3-1-100-4
          -7-100-4000-1-5
          -8-20-10-100-20
          -900-2-100-1-3-1
          -10-1-1-3-2000-4-2
          -11-2-2-40-10-1-50
          0-3-30-1-20-2-60
          00-4-10-30-3-7-1
          00-50-10-1-4-8-2
          -1-1-600-10-500
          -2-20-1-10-10-10
          -3-3-10-2-10-1-20
          -4-4-2-1000-2-30

            United States
            Member #124493
            March 14, 2012
            7023 Posts
            Offline
            Posted: January 6, 2013, 7:55 pm - IP Logged

            I'm glad you posed this question. I've learned something today. you in fact can combine text and formulas in the same cell.  in the cell you want your results,

            ="E "& sumproduct((mod(b1:f1,2)=0)+0)&" O "& sumproduct((mod(b1:f1,2)<>0)+0)

            b1:f1 is your data range adjust as needed.

            I put this formula and it brought back a value, I just am not sure if the value matches up

            Do i have to put a range?  Or can i just put one cell value?

              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: January 8, 2013, 5:55 pm - IP Logged

              Array formula?


                United States
                Member #124493
                March 14, 2012
                7023 Posts
                Offline
                Posted: January 8, 2013, 6:32 pm - IP Logged

                Array formula?

                What?

                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  460 Posts
                  Offline
                  Posted: January 8, 2013, 7:03 pm - IP Logged

                  in your example you did not specify what col or row your information was located. I had to assume a couple of values(  b1:f1  )  if your data is in a different location then you need to change these references to match your data locations. the good news you only need to do it once.  after that you  can copy and paste


                    United States
                    Member #124493
                    March 14, 2012
                    7023 Posts
                    Offline
                    Posted: January 8, 2013, 7:36 pm - IP Logged

                    in your example you did not specify what col or row your information was located. I had to assume a couple of values(  b1:f1  )  if your data is in a different location then you need to change these references to match your data locations. the good news you only need to do it once.  after that you  can copy and paste

                    Thanks phileight.

                    I put the proper range, and so far its working like a charm.Thumbs Up

                    Now if I can only make sense of what you typed!Eek

                      Avatar

                      United States
                      Member #41846
                      June 23, 2006
                      460 Posts
                      Offline
                      Posted: January 9, 2013, 5:51 am - IP Logged

                      Thanks phileight.

                      I put the proper range, and so far its working like a charm.Thumbs Up

                      Now if I can only make sense of what you typed!Eek

                      I understand it well enough to use, but to explain it i'm not sure.  I would not use this, I would write a vba routine to do it.  for me much easier to understand

                      the mod function will reduce whatever you feed it to something less than what you specify as a divisor. in this case the divisor is 2 so the answer for each digit in your range is either 0 or 1. 0 its an even #,  1 its an odd #.  sum product is an array function that counts how many times something occured that matches what you specify, in this case 0 for even #,  and if its not 0 (<> 0) it must be odd.  the range tells it where to get the information

                      now you have a # and to combine that with something else use &. when you string all that together, magic or so it seems sometimes

                      if you want more details on these functions use excel's help files. they are frequently more confusing than helpful but there is a lot of help there.

                        SergeM's avatar - slow icon.png
                        Economy class
                        Belgium
                        Member #123700
                        February 27, 2012
                        4035 Posts
                        Offline
                        Posted: January 9, 2013, 8:32 am - IP Logged

                        The other way but I would use an array formula in a normal case.

                          Avatar

                          United States
                          Member #41846
                          June 23, 2006
                          460 Posts
                          Offline
                          Posted: January 9, 2013, 8:51 am - IP Logged

                          The other way but I would use an array formula in a normal case.

                          what version of Excel are you using?  this is not an option in 2007 version.  it certainly would be easier

                            SergeM's avatar - slow icon.png
                            Economy class
                            Belgium
                            Member #123700
                            February 27, 2012
                            4035 Posts
                            Offline
                            Posted: January 9, 2013, 8:54 am - IP Logged
                            I use Excel 2010.

                            I use Excel 2010.