+ Reply to Thread
Results 1 to 16 of 16

Help conditional formatting every next cell of column

  1. #1
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Help conditional formatting every next cell of column

    hi guys i am new here and i really need help i cant explain so i just made an illustration to illustrate what i wanted to do

    CIPhnhf.jpg

    what i want to do is detect every next number and see if its high and low then count

    thank you in advance

  2. #2
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Help conditional formatting every next cell of column

    If you want it all in one formula, that's a little more complicated than im willing to attempt right now. However, if you can do it in steps (helper columns), it isn't that bad.

    =countif(A2:A21,"<50") would count your lower values.

    =mod(row(a2),2) in C then double click down gives a helper column telling if they are odd or even (every other row).

    so then you can use:

    =countifs(A2:A21,"<50",C2:C21,1)

    to determine if low and if it is on every other row as desired.

    if you want it to print out results on the side as you have, you could approach it a different way.

    =if(mod(row(a2),2)=1,if(a2>=50,"higher","lower"),"") in the B column

    then you could use

    =countif(b2:b21,higher)

    and


    =countif(b2:b21,lower)

  3. #3
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    wow thats fast thank ill try this right now :D

  4. #4
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    hi i was trying to play with the example code you given to do another task but i cant seem to figure it out how i almost played with it for 2 hours pls refer to screenshot

    bWK6BWy.jpg

    what i was trying to do here is that detect numbers from 90 above and tell if the value next to it is higher or lower

    thanks

  5. #5
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    Write this formula on B2 and copy down :
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    sorry i think i now know what i want to do actually i am sorry if i am being spoonfed but glad to find out that there is an active community dedicated to help but still im new to excel so pls bear with me and help me accomplish what i need ..

    pls see screenshots

    oP34MYq.jpg
    T5JziYx.jpg
    MiylTe1.jpg
    6CqP4MW.jpg

    i am having hard time to explain thats why i chose to make screenshots instead to explain what i want to accomplish on the screenshots i want to find certain numbers and detect whether the value next to it is high or low, i made 4 separate screenshots just to make it look simple cause i dont want to have a lot of arrows :P but its supposed to be in 1 page

  7. #7
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    This formula will give you that result :
    Please Login or Register  to view this content.
    This will count the higher value from row 2 to 21. Change it as you want. If your starting row is 3 then change A1 to A2 and A2 to A3. If your ending row is 50 then change 21 to 50 and 22 to 51.

  8. #8
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    If you want to show the words "Higher" and "Lower" beside then write this formula in B2 and copy down.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    Quote Originally Posted by sanram View Post
    This formula will give you that result :
    Please Login or Register  to view this content.
    This will count the higher value from row 2 to 21. Change it as you want. If your starting row is 3 then change A1 to A2 and A2 to A3. If your ending row is 50 then change 21 to 50 and 22 to 51.
    what if i want the whole column? can i just do it? as A:A?

  10. #10
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    No. This formula will not work with whole column.

  11. #11
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    This will not be a good choice to use that formula on whole column. But I providing it as you want :
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    ok thanks i got over 3000 numbers and have to verify if its working correctly but really i appreciate your time and effort :D

  13. #13
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    You are welcome and thanks for the rep.

  14. #14
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    if i can only donate i would anyway is it possible to just get the value to be compared on a cell? example

    vbrSNg9.jpg

    so i just have to drag it rather than type the numbers over and over again

  15. #15
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help conditional formatting every next cell of column

    Yes. But you have to use this in this way :
    Please Login or Register  to view this content.
    Here C8 is lower value, C9 is Upper value and C10 is 50.

  16. #16
    Registered User
    Join Date
    09-10-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    17

    Re: Help conditional formatting every next cell of column

    thank you guys closing this thread now as i have another question :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting. If cell in column A=x, then any entry in column B must include y
    By rnbblsmm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2015, 05:17 PM
  2. Replies: 3
    Last Post: 11-11-2013, 09:36 AM
  3. Replies: 3
    Last Post: 04-17-2012, 05:33 PM
  4. Replies: 2
    Last Post: 10-01-2011, 09:45 AM
  5. [SOLVED] Conditional formatting when cell value is top 3 of column . . .
    By Hubitron2000 in forum Excel General
    Replies: 3
    Last Post: 03-21-2006, 05:45 PM
  6. Conditional formatting row if cell value in certain column is even
    By Hyland Hunt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08854 seconds
  • Memory Usage 9,296KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (6)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (16)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (16)postbit_legacy
  • (16)postbit_onlinestatus
  • (16)postbit_wrapper
  • (4)showthread_bookmarksite
  • (6)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1