Pennsylvania United States
Member #109,270
April 9, 2011
1,628 Posts
Offline
Quote: Originally posted by LottoBoner on Jan 6, 2013
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 #124,487
March 14, 2012
7,021 Posts
Offline
Quote: Originally posted by omiller315 on Jan 6, 2013
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 #41,845
June 23, 2006
713 Posts
Offline
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 #124,487
March 14, 2012
7,021 Posts
Offline
Quote: Originally posted by phileight on Jan 6, 2013
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.
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 #124,487
March 14, 2012
7,021 Posts
Offline
Quote: Originally posted by phileight on Jan 6, 2013
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?
United States
Member #41,845
June 23, 2006
713 Posts
Offline
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 #124,487
March 14, 2012
7,021 Posts
Offline
Quote: Originally posted by phileight on Jan 8, 2013
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.
United States
Member #41,845
June 23, 2006
713 Posts
Offline
Quote: Originally posted by LottoBoner on Jan 8, 2013
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.