Welcome Guest
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
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.

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)

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)

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.

 -1 0 0 -1 0 0 -4 -1 -1 -1 -2 -1 0 0 0 -1 -5 -2 0 0 -3 0 0 -1 0 -2 -6 -3 0 -1 -4 0 0 0 -1 -3 0 -4 0 -2 -5 -1 0 0 -2 0 0 -5 0 -3 -6 0 -1 0 -3 -1 -1 0 0 -4 -7 -1 0 0 -4 0 0 0 -1 -5 -8 -2 0 -1 0 -1 0 0 -2 0 -9 0 0 -2 -1 0 0 -1 -3 -1 -10 -1 -1 -3 -2 0 0 0 -4 -2 -11 -2 -2 -4 0 -1 0 -1 -5 0 0 -3 -3 0 -1 -2 0 -2 -6 0 0 0 -4 -1 0 -3 0 -3 -7 -1 0 0 -5 0 -1 0 -1 -4 -8 -2 -1 -1 -6 0 0 -1 0 -5 0 0 -2 -2 0 -1 -1 0 -1 0 -1 0 -3 -3 -1 0 -2 -1 0 -1 -2 0 -4 -4 -2 -1 0 0 0 -2 -3 0

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)

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?

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?

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.

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

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.

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

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.

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.

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

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.

 Page 2 of 2