Excel 2007 FUNCTIONS

Question

This is where I am at: I created a new workbook ... with two sheets.

The first I named SALES ... the second TO BILL.

In the first one I have my customers f&l, what they purchased and the purchase price ... 4 different columns.

In the second (TO BILL) I used the insert function which automatically enters the customer in the same position from sheet one. Next column I have the amount paid. The column after that I have the balance due ... that I used function to automatically calculate the purchase price from sheet one, less the amount paid from sheet two.

Now ... problem ... can't figure this out with IF or LOOKUP. The next column is INVOICE and I want to automatically have a YES value inserted IF the balance due is greater than 0.

??? How do I do that?

The first row looks like this ...well second row ... I've frozen row one with the column names.

L Name | F Name | Paid | Balance | Invoice |

So my first entry is on line two ... the function I need is in E2. If there is an amount greater than 0 in D2 ... I need it to insert YES into E2??? If not ... than blank or no can be in there but not necessary. Does that make sense?

So ... I click in E2 ... select insert function ... select IF ... then I am lost... or confused from trying everything I can think of and getting nothing but errors. The one time I thought it was working ... I dragged the formula down ... but it put YES in every field.

When I click to insert function ... the window pops up with the following 3 lines that I need to insert something:

Logical_test ___________________________________ = logical

Value_if_true __________________________________ = any

Value_if_false _________________________________ = any

then click okay!

Anyone??? Thanks in advance!

Message Edited by Moto-Mgmt. on 11-07-2007 09:37 PM

Message Edited by Moto-Mgmt. on 11-07-2007 09:38 PM

ISGuy · Answer

I hope that this is what you want. Put this into E2   =IF(D2>0,'YES','')   In this case, anything that's not a negative number in D2 will cause a YES in E2.

Moto-Mgmt. · Answer

ALMOST ... So how do I stop getting the value YES for 0 balance due... even if it means entering a NO ...at least I will be able to data sort that for billing.

MORE THAN 0 = YES ... and
Equal to or less than 0 = NO or blank

... can you see I am sitting right here in desperation ???

Thanks ... Lisa

ResDirAca · Answer

Actually, this might work:   =IF(D2 > 0, 'Yes', 'No')   Not sure if the fact that you left the 2nd variable blank may have defaulted to your only answer?   If adding the other option in there doesn't work, try bumping the 0 to a 1. Message Edited by ResDirAca on 11-08-2007 01:47 AM

ResDirAca · Answer

Have you tried changing the 0 in your formula to 1?   Edit: Ignore this and try the one below. Message Edited by ResDirAca on 11-08-2007 01:35 AM

Moto-Mgmt. · Answer

Thank you ... I have tried this ... I get a YES for a 0 balance as well. Just to error shoot which is confusing me even more ... I eliminated the calculated field function ... entered a hard 0 and then I get the result I am looking for.

ISGuy · Answer

=IF(D2>0,'YES','')    or   =IF(D2>0,'YES','NO')    works for me. If I put a hard zero in D2, then E2 remains blank or puts in a NO depending upon which formula from above you choose. Any number greater that zero in D2, such as .00001, makes E2 say YES. Negative numbers in D2 show up as NO in E2, as expected.   I'm not sure why you're having the issue if you copy and paste what has been posted here and don't type it in yourself just one time to test it out.   Hope this helps. Where is VisiCalc when you need it?   http://en.wikipedia.org/wiki/Visicalc

Moto-Mgmt. · Answer

Thank you ... I do appreciate your help. I have tried the other option you mention as well. This has always worked for me in my previous versions.

My actual calculations: Both sheets row one is stationary ... always visible and names my columns. All data entry begins in row two. All $$$ are whole ... no cents.

Sheet1 Named SALES has 4 columns: All fields are manually entered.
L Name | F Name | Item | Cost

Sheet2 Named RECEIVED has 5 columns:

L Name | F Name | Paid | Balance | Invoice

Calculations/functions stated here:
Column1 Row2 auto enter from sheet1 ... used =LOOKUP("Sheet1!",Sales!A2,Sales!A2)
Column2 Row2 auto enter from sheet1 ... used =LOOKUP("Sheet1!",Sales!B2,Sales!B2) Column3 Row2 manual entry amount paid ...

Somewhere from this point on, something is wrong...

This (column4) works ... The difference from the cost of the item and the amount paid ...

Column4 Row2 auto enter ... used =IMSUB(Sales!D2,C2)

This doesn't ... Column 5 ... need a YES to display if there is a balance owed. And this is where I am stuck. If I manually enter a HARD ZERO it works ... not working with balance of zero from a calculated field. ???

Message Edited by Moto-Mgmt. on 11-08-2007 11:40 AM

Moto-Mgmt. · Answer

Yes ... I have copy/pasted every scenario. ...except I changed D2 to D4 since I incorrectly posted D2.

ResDirAca · Answer

Ok, that's why it worked for me, I was using an entered value, not a calculated value.   Is it possible that the calculated value is actually a rounded number that shows as 0 but is actually 0.001 or something stupid like that?  If that's the case, by changing the 0 in the formula to .0049, maybe that might solve the issue?  Again, just throwing ideas out here, not sure if it will work since I'm not using your actual calculations to play around with.

abach · Answer

In your first worksheet (Sales), you seem to have     If so, your second worksheet (Received ) should have the following formulas - There is no need to use the Lookup function, just point the cells to the other sheet. (I've placed the formula examples below the cells)

Moto-Mgmt. · Answer

WhooHoo ... THANK YOU! THANK YOU! Lisa

Productivity Software

Excel 2007 FUNCTIONS

Was this post helpful?