Mathematical code

[RJ1969]

Hi,
I have this code:
=IF(AND(ISNUMBER(B7),ISNUMBER(B7)),B7+C7+D7+E7+F7,"")

Instead of doing B7+C7+D7+E7+F7, is there some way to combine them.
Thanks.

[Norie]

Perhaps.

=IF(AND(ISNUMBER(B7),ISNUMBER(B7)),SUM(B7:F7),"")

[Richard Buttrey]

Why are there two identical ISNUMBER(B7) functions in and AND condition? What's the purpose?

[RJ1969]

Hi
I am actually adding B7:F7 then minus G7. Can you edit the code? Thank you.

[Pete_UK]

Try this:

=IF(ISNUMBER(B7),SUM(B7:F7,-G7),"")

Hope this helps.

Pete

[Sam Capricci]

how about =SUM(B7:F7)-G7

[RJ1969]

this code is perfect =IF(ISNUMBER(B7),SUM(B7:F7,-G7),"")

but how do I set the text when I get a negative in red font? example -2.

thanks.

[Pete_UK]

What do you mean by "set the text"? What do you want to happen if the answer is negative (or if B7 is negative - it's not clear what you mean).

Pete

[RJ1969]

Hi
With this code:
=IF(ISNUMBER(B6),SUM(B6:B7,-B8),"")

Example:
Carry over (B6) …. 2
plus
Received (B7)….. 5
minus
Processed (B8)….. 8
The answer is: -1
Then it should turn red.

[Pete_UK]

One of the standard formats can be chosen to do that for you. Select the cell with the "answer" in it, then right-click and choose Format Cells | Number tab | Number, and then choose how you want negative numbers to be displayed (you can choose black or red, with or without the minus sign). Then click OK.

You can use the Format Painter icon to copy that format to other cells.

Hope this helps.

Pete

[RJ1969]

Can this be set in a conditional format?
I want to keep my answer in black font but if I get a negative answer then it should answer red font.

thanks.

[Pete_UK]

There is no need to use Conditional Formatting - the instructions I gave you will do what you want.

Pete

[RJ1969]

Hi Pete,
I did follow your instruction. I click the cell where the formula is then I right click and chose "number" then I selected the one with the red. But no matter what the answer is negative or no negative , it stay in same color.

Is there no other way ?

[Pete_UK]

You can use Conditional formatting if you wish to. Select the cell with the formula in (assumed to be B9 from the above), then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box:

=B9<0

Then click on the Format button | Font tab and choose red from the Color drop-down, then OK your way out.

Hope this helps.

Pete

[RJ1969]

Hi,
I attached the file. It will work if I removed the color tab code in VBA code in sheet1. I want to keep the color tab code.

Thank you.

[Pete_UK]

I've set up the formatting for you in cells B9:G9 (and on other subtotal rows below), using normal formatting showing negative numbers in red with a leading zero, as described in Post #10.

Hope this helps.

Pete

EDIT: It helps to attach the file:

[RJ1969]

Thank you Pete !!
and thank you all.

[Pete_UK]

I should have said "with a leading minus sign" in my previous post.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete