topic Re: Conditional Formattign in Excel in QlikView

Conditional Formattign in Excel

Fri, 10 Jul 2015 13:53:00 GMT

Is there any way to create a dynamic IF() statement within Excel that will color cells based on their sum. Thus I want a formula that simply states that is B1 - C1 > 0, Green(), Red(), and I want it applied to $B$1:$C$50. Is this possible? I am comparing the computed value for each row and thus will color the cell accordingly.

Thanks for any and all help.

Best.

Re: Conditional Formattign in Excel

sinanozdemir — Fri, 10 Jul 2015 13:57:16 GMT

Hi Michael,

Are you asking whether or not this can be implemented through QlikView or how to do conditional formatting in Excel?

Thanks

Re: Conditional Formattign in Excel

sunny_talwar — Fri, 10 Jul 2015 13:58:08 GMT

Are you asking a Excel question on QlikView Community? I am sure someone will have an answer, but why not browse it on the internet?

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 13:58:17 GMT

In Excel

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 13:58:47 GMT

yes and I did

Re: Conditional Formattign in Excel

sinanozdemir — Fri, 10 Jul 2015 13:59:50 GMT

Ok.

There is conditional formatting option in Excel:

You can create your own rules.

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:03:15 GMT

I know that, however, I need help with the IF() statement itself, because I am not simply comparing all cell values to a single value, but rather the product of Cell 1 - Cell 2, which will then determine what color each cell should be.

Re: Conditional Formattign in Excel

sunny_talwar — Fri, 10 Jul 2015 14:10:06 GMT

See if this link helps: MrExcel Message Board

Best,

Sunny

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:18:31 GMT

It shows you how here... I used it yesterday goodluck

https: //support.office.com/en-za/article/Use-a-formula-to-apply-conditional-formatting-in-Excel-2013-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?ui=en-US&rs=en-ZA&ad=ZA

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:26:21 GMT

Completely agree this is a QLik community for Qik related questions. Although somebody will have the answer this is not the right place to ask

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:28:04 GMT

I figured it out, if I want to compare the outcome of B1 - C1 for B1:C50, then within the Conditional Formatting Rules Manager you cannot hard code =($B$1-$C$1)<0, nor =($B$1:$B$50-$C$1:$C$50)<0, but rather =($B1-$C1) < 0, to keep the calculations in column $B and $C. This will then calculate B1 - C1 .......... B50 - C50, and if < 0, will format the cells RED, then you need to create an additional Rule that states =($B1-$C1) >= 0, to format the cells GREEN.

Best

Re: Conditional Formattign in Excel

sinanozdemir — Fri, 10 Jul 2015 14:30:40 GMT

Just use VBA, please see below screenshot:

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:31:37 GMT

no harm no foul

Re: Conditional Formattign in Excel

Fri, 10 Jul 2015 14:32:50 GMT

Much easier, thanks mate

Re: Conditional Formattign in Excel

sinanozdemir — Fri, 10 Jul 2015 14:38:03 GMT

You are welcome.