• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

More trouble with cell code.

J

Jared P.

New Member
Hello all that can help,

I'm desperate. I need ONE string of code that can do all of the following. I have been working all night on trying to figure this out, and I cannot.

My code needs to count ONLY cells on the right that have data to their immediate left, but NOT cells on the right that do not have data to their left. With that being said, it also needs to produce a count for ONLY the number of those narrowed cells that have a value GREATER than the cell to its left. And then that needs to be produced as a quotient of those even more narrowed cells over the total number of cells in the column to its left. I will elaborate more in the spreadsheet.

Thanks, please help me.
 

Attachments

  • Sample.xlsx
    10.2 KB · Views: 2
Hi Jared,

Use the below...I havent validated them though...

2012vs 2013: SUM(--IF(A2:A22<>"",B2:B22>A2:A22))/COUNTA(A2:A22)
2013vs2014: SUM(--IF(B2:B22<>"",C2:C22>B2:B22))/COUNTA(B2:B22)
2012vs2014: SUM(--IF(A2:A22<>"",C2:C22>A2:A22))/COUNTA(A2:A22)

Must be acknowledged with CTRL + SHIFT + ENTER and not just ENTER
 
Asheesh, or anyone who can help.

I am still having a lot of trouble. It wasn't giving me the right quotient. Please see the attached spreadsheet with the complete set of data. You can try the formulas you provided and see if you get the same results I got counting by eye (in the new spreadsheet), but I could not get them to work.

NOTE to any new posters: Please refer to my original attachment on my first post if you have any trouble understanding.

Thank you
 

Attachments

  • Complete Data Sample.xlsx
    36.4 KB · Views: 4
Hi ,

Your manual figures are slightly off ; there seem to be 137 cases where the values in column C are greater than the corresponding values in column B.

Can you see the attached file and comment ?

Narayan
 

Attachments

  • Copy of Complete Data Sample.xlsx
    72.3 KB · Views: 4
NARAYANK991 said:
Hi ,

Your manual figures are slightly off ; there seem to be 137 cases where the values in column C are greater than the corresponding values in column B.

Can you see the attached file and comment ?

Narayan
Click to expand...

Yes, you appear to be correct, with the exception of cell C13 in which it is equivalent to cell B13 (0 and 0). So actually 136.

Thanks, I'm hoping you can figure this code out for me!
 
NARAYANK991 said:
Hi ,

Your manual figures are slightly off ; there seem to be 137 cases where the values in column C are greater than the corresponding values in column B.

Can you see the attached file and comment ?

Narayan
Click to expand...
Did you understand my directions okay? I've been trying to get this right all day and I am just hoping somebody knows exactly what my brain is trying to get at
 
Hi ,

The problem with the two zeros is the cell format ! One of the zeros is not exactly 0. Check out the value in the cell.

Narayan
 
NARAYANK991 said:
Hi ,

See if these calculations are correct.

Narayan
Click to expand...
Narayan and Asheesh,

This seems to be working perfectly. Thank you so much! The weird thing is, I got one of those numbers somehow earlier using the other code too! But at the time I just figured it was wrong again...So thank you both of you for your help. I really really appreciate it.
 
You must log in or register to reply here.
Back
Top