I have a range of cells (I7:I42 ).
The total of which, I would like to subtract from the value of cell J43.
If the total of the range (I7:I43) is greater than J43,
I want the difference to display in C3 as a positive number.
Any help would be greatly appreciated.
see ABS function, eg:
=ABS(J43-SUM(I7:I43))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you, the ABS function did work, but it does not meet my requirements
"If the total of the range (I7:I43) is greater than J43,
I need the "difference" to display in C3 as a positive number.
eg: "it the cell range total (I7:I42) = 25 and the cell total (J43) =
22, then I need the number 3 to be displayed in appropriate cell.
Thank You again for you help.
Which is it? If not I7:I43, thenI have a range of cells (I7:I42 ).
The total of which, I would like to subtract from the value of cell J43.
If the total of the range (I7:I43) is greater than J43,
=ABS(J43-SUM(I7:I42))
Entia non sunt multiplicanda sine necessitate
Either range, the result needs to be the same.
C3 needs to be either Zero or the greater difference between the total of the range
and the reference cell
eg:"if the cell range total between (I7:I42) = 25 and cell J43 = 22, I then I need the greater difference "3" to be displayed in the appropriate cell of C3".
"or"
eg:"if the cell range total between (I7:I42) = 15 and cell J43 = 22, I then I need "0" to be displayed in cell C3".
"or"
eg:"if the cell range total between (I7:I42) = 100 and cell J43 = 22, I then I need the greater difference of "78" to be displayed in the appropriate cell of C3".
Last edited by Ashone; 04-17-2010 at 12:13 PM.
Maybe just =SUM(I7:I43) - J43
shg ...
Thanks, but still no help....
Thanks, but still not working...Originally Posted by shg
can you upload example with your input and desired output and also describe what doesn't work?
disregard last upload
Last edited by Ashone; 04-17-2010 at 02:39 PM.
my turn
perhaps
=MAX(0,SUM(I7:I43) - J43)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
HELP.xlsx
This is the correct example worksheet.
martinwilson....
You are VERY, VERY close
CORRECTION to Worksheet
I need formula for B8, E8 and H8
Sorry for all the confusion, And Thank you for all your help...and patience
very close?
for your sheet you'd need in c8
=MAX(0,SUM(B1:B6)-C7) or even just =MAX(0,B7-C7)
Hip, hip, hurray...i've found my excel hero.
Thanks a Million
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks