Formula help needed
Solved/Closed
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
-
Mar 17, 2013 at 08:34 AM
cromwelld Posts 6 Registration date Sunday March 17, 2013 Status Member Last seen March 20, 2013 - Mar 20, 2013 at 08:14 PM
cromwelld Posts 6 Registration date Sunday March 17, 2013 Status Member Last seen March 20, 2013 - Mar 20, 2013 at 08:14 PM
Related:
- Formula help needed
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Credit summation formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 17, 2013 at 11:09 AM
Mar 17, 2013 at 11:09 AM
I am not sure why you would you need an array formula if all you want to add those rows where you have "LOCATION"
if that is the case then instead of
SUM(IF(F2:F50="LOCATION",B2:B50))
use
SUMIF(F2:F50,"LOCATION",B2:B50)
Now on to your question. I am not sure I got what you meant by "deduct the amount in column B".
if you are saying that you want to add B2:B50 if F2:F50 is location and not some text in E2:E50 then in that case you can use
=SUMPRODUCT(1 * (F2:F50="LOCATION") * (E2:E50<>"SOME OTHER TEXT") * (B2:B50))
if that is the case then instead of
SUM(IF(F2:F50="LOCATION",B2:B50))
use
SUMIF(F2:F50,"LOCATION",B2:B50)
Now on to your question. I am not sure I got what you meant by "deduct the amount in column B".
if you are saying that you want to add B2:B50 if F2:F50 is location and not some text in E2:E50 then in that case you can use
=SUMPRODUCT(1 * (F2:F50="LOCATION") * (E2:E50<>"SOME OTHER TEXT") * (B2:B50))
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
Mar 17, 2013 at 04:37 PM
Mar 17, 2013 at 04:37 PM
Yes, by deduct, I really meant exclude... Sorry..
And all of your formulas worked perfectly! Thank you very much for the quick reply!
And all of your formulas worked perfectly! Thank you very much for the quick reply!
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
Mar 17, 2013 at 04:58 PM
Mar 17, 2013 at 04:58 PM
Now, this whole spreadsheet has me thinking...
What if I would want the value for those fields that the formula is executed in to change font colors based on whether or not the value is a) lower than an expected amount, b) the same as the expected amount, or c) higher than the expected amount.
So lets say we have the formula above executed in L4 to provide a total for location ABC. In L3 is an expected total of $1,250,000.00 Let's say L3 is $1,000,000.00. So, since it is less than what was expected in L4, I want the font color to be red. If L4 is the same as L3, I want the font color to be green. If L4 is higher than L3, I want the font color to be blue.
Is this possible?
What if I would want the value for those fields that the formula is executed in to change font colors based on whether or not the value is a) lower than an expected amount, b) the same as the expected amount, or c) higher than the expected amount.
So lets say we have the formula above executed in L4 to provide a total for location ABC. In L3 is an expected total of $1,250,000.00 Let's say L3 is $1,000,000.00. So, since it is less than what was expected in L4, I want the font color to be red. If L4 is the same as L3, I want the font color to be green. If L4 is higher than L3, I want the font color to be blue.
Is this possible?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 17, 2013 at 06:27 PM
Mar 17, 2013 at 06:27 PM
Yes it can be done. In excel it is called "Conditional Formatting"
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
Mar 17, 2013 at 09:36 PM
Mar 17, 2013 at 09:36 PM
I have found the Conditional Formatting option (Format, Conditional Formatting), but I can't seem to figure out the rule.
I clicked on + to add a rule. I then choose 3 Color Scale. From there I am not sure if I am supposed to choose number, lowest value, highest value, or what... I did numbers for all three, selected the colors for the font, but I can't seem to get it to work correctly...
I clicked on + to add a rule. I then choose 3 Color Scale. From there I am not sure if I am supposed to choose number, lowest value, highest value, or what... I did numbers for all three, selected the colors for the font, but I can't seem to get it to work correctly...
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 19, 2013 at 09:01 PM
Mar 19, 2013 at 09:01 PM
Based on what you said
lets say
L4 = sum as per formula
L3 = expected value
You would need three rules. When you go to conditional format, you need to use formula
for blue, you need to enter =L4>L3
for RED, you need to enter =L4<L3
for GREEN, you need to enter =L4=L3
lets say
L4 = sum as per formula
L3 = expected value
You would need three rules. When you go to conditional format, you need to use formula
for blue, you need to enter =L4>L3
for RED, you need to enter =L4<L3
for GREEN, you need to enter =L4=L3
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
Mar 20, 2013 at 07:17 AM
Mar 20, 2013 at 07:17 AM
I have entered three separate rules. Each one is set up as Classic, Use a formula to determine which cells to format. I have chosen the font color and fill color and have entered the formula in the appropriate cell; however, the font color in my sheet hasn't changed...
And why mark solved without allowing me, the OP, to mark it solved?
And why mark solved without allowing me, the OP, to mark it solved?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 20, 2013 at 11:45 AM
Mar 20, 2013 at 11:45 AM
I marked it solved as your query was "Now, I am looking to do the same thing, but, I want to deduct the amount in column B IF the value in column E equals a certain text."
and you said "
Conditional formatting was add on which I believe should have been another question.
But any ways on your color not showing, did you select any color would be obvious question to ask. If you did, then do this. Enter your condtional formula on a sheet and see if you get "True" or "False" as per your expectation. If not, you would need to see why i guess
and you said "
And all of your formulas worked perfectly! Thank you very much for the quick reply!"
Conditional formatting was add on which I believe should have been another question.
But any ways on your color not showing, did you select any color would be obvious question to ask. If you did, then do this. Enter your condtional formula on a sheet and see if you get "True" or "False" as per your expectation. If not, you would need to see why i guess
cromwelld
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
Mar 20, 2013 at 08:14 PM
Mar 20, 2013 at 08:14 PM
I deleted and reapplied the rules, and for some reason they started working. I didn't change anything, so I am not sure why it didn't work the first time...
Thank you for all of your help!
Thank you for all of your help!
