How to compare the sum of two cells in an expression column with a user input value???

[#1]

I was trying to compare the sum of two cells in a an expression column which is defined as(vSum=(sum(A)/1000=sum(B)/1000) and these two cells corresponds to the  'Category_1' and 'Category_2'  text in the dimension column.

Is there any way to resolve this scenario???

[jagan]

Hi John,

Can you attach some sample file and come up with your required output.

Regadrs,

Jagan.

[amit_saini]

John,

Did you mean something like this below???

if (sum(Category_1)=sum(Category_1), 'Yes','No') (Use as an expression)

Thanks,

AS

[#4]

Hi Jagan,

Thank You for ur immediate reply.

Please find attached a sample excel file with the different scenarios.

[jagan]

Hi,

Try like this

For Input 1:

=If(vInputValue1 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), Red())

For Input2:

=If(vInputValue2 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), RGB(255, 128, 0))

vInputValue2 and vInputValue1 are variables which holds Input box values and A is the dimension which holds 'Category_1', 'Category_2' values.

Hope this helps you.

Regards,

Jagan.

[#6]

Thank you Jagan...it did work....but can we combine both these conditions as a nested if statement??

So both input values are evaluated and the background color of the 'Estimated Hours of Work cell' with respect to 'Category_1' and 'Category_2' is highlighted red and orange.

And I came across one more problem...Is there any function to sum the two cell values of  'Estimated Hours of Work with respect to 'Category_1' and 'Category_2' and compare it against the input box values??

[jagan]

Hi,

Try like this

=If(vInputValue1 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), Red(),

If(vInputValue2 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), RGB(255, 128, 0)))

=Sum({<A={'Category_1' , 'Category_2' }>} TOTAL EstimatedHours)

Regards,

Jagan.

[#8]

The set analysis statement for summing up the row values is not working ,may be because the Column Estimated Hours calculation is assigned to a variable???

Any thoughts!!

[#9]

How can we use an expression which is assigned to a variable in set analysis???@

[#10]

Hi Jagan,

Your code was kind of life saver,but one more enhancement required.

vInputValue1 > Sum(EstimatedHours)

vInputValue2 >Sum(EstimatedHours)

How can I add these two condition into the same If statements.

Want to give the cell background color as default white()