IF statement for a Sum is greater than another number

[stevejbailey]

Hi. My name is Steve and from the UK. My Excel knowledge is basic. Here is a problem i've been trying to solve without success.

My spread sheet is for a building project.

I have to have a figure in a cell used for contingency sum used which would only be shown if i have used any of the contingency.

Allowance - B4
Previously drawn - B5

Total Drawn To Date - B48
Remaining Balance - B50
Contingency Required - B52

I need to show a figure (£) in B52 if B50 is greater than B4. If it isn't then the cell is left blank.

Its probably very simple but i cant figure it out. Ideally the figure would be high lighted red.

I hope i have made myself clear, i know it confuses me.

[:) Sixthsense :)]

In B52 Cell

Please Login or Register  to view this content.

[stevejbailey]

Thanks for your help but it didn't seem to do anything

[XOR LX]

Hi Steve,

"I need to show a figure..."

You don't say how this figure should be calculated?

Regards

[stevejbailey]

I am probably not explaining it very well, sorry.

I need to show a sum in B52 if B50 is greater than B4. If it isn't then the cell is left blank.

B52 is a contingency sum of money. B50 is a remaining balance and B4 is the allowance money.

[XOR LX]

"I need to show a sum in B52"

But the sum of what?! Of which cells?!

Regards

[stevejbailey]

Sorry, it's just me not explaining very well.

My doc is made up as follows.

Allowance (A)
Previously Drawn (B)
Total of all invoices (D)

Total drawn to date B+D=E
Remaining Balance A less E = F
Contingency Required. If F is greater than A (in this cell only the number would be shown if a contingency use is required)

[XOR LX]

No, Steve, it was better with cell references as before, but you have to understand that I still don't know what values make up your sum.

Let's try it like this and then we are nearly there in Excel terms:

In B52: "If B50 is greater than B4 then perform sum of [insert cell references], otherwise leave blank"

If you can fill out the above then we are nearly there!

Regards

[stevejbailey]

Thank you for your patience. Still not sure what to give you.

My Doc

Allowance - B4
Previously Drawn - B5
Total of invoices - B46 (sum of B10 to B44)
Total drawdown to date - B48
Remaining balance - B50
Contingency required - B52

Total drawn to date B5+B46=B48
Remaining Balance B4 less B48 = B50
Contingency Required. If B50 is greater than B4 (in this cell only the number would be shown if a contingency use is required)

Not sure if this is clear (probably as clear as mud).

[XOR LX]

I'm really sorry but I think we're not on the same wavelength here for whatever reason so I'm going to try one last time and go right back to your original statement:

"I need to show a figure (£) in B52 if B50 is greater than B4"

In your words, tell me precisely how this "figure" is to be determined. I appreciate that you have listed several other variables in your sheet, so if it includes these then by all means spell it out.

Example (completely hypothetical):

"If B50 is greater than B4 then take sum of Total of invoices and Total drawdown to date and subtract Remaining balance".

If you can put it into words, then we can translate into Excel. But what I (at least) can't do is tell you what you should be summing (if B50 is greater than B4) - that must come from you.

Regards

[stevejbailey]

It must be frustrating for you to try and help a complete novice when it comes to excel.

Its a building project i am currently doing. The QS overseeing the draw down of funds has asked for this.

We have the invoices coming in for work undertaken in a specific area, i.e groundworks.

The allowance for this work is say £100000 (cell B4). Previous draw down amount, say £25000 (cell B5). Invoice/Supplier list (Cells B10 to B44) with the total invoice amount (cell B46) say comes to £74000.
Total drawn to date (cell B48) is £25000+£74000=£99000
Remaining balance (cell B50) is £1000
Contingency required (cell B52) is therefore £0. But if the invoices added up to £76000 this would make the Total drawn to date £101000 and this would make the Contingency required in (cell B52) show a figure of £1000
I only want to show a figure in the 'Contingency Required' cell if i have actually used any of it.

Hope that makes sense.

[XOR LX]

Ok, so it appears that you simply want in B52:

=IF(B50<0,-B50,"")

(I've assumed the formula you already have in B50 is: =B4-B48)

Regards

[stevejbailey]

That's it. Thank you very much.

[XOR LX]

You're welcome. Glad we got there in the end!