Microsoft excel equations

[drathzel]

I am doing some accounts and i am trying to do the equation that " if paid in F10 then - J10" example!!! Does anyone knwo what this equation is? i have used it before in my previous job but now i am doing it myself i cant remember!

Help anyone?

[Gojive]

Danielle,

I'm doing this from memory, so it may be wrong!

I think what you're looking for, is:

=if(F10,J10,"")

[drathzel]

Originally Posted by Gojive

Danielle,

I'm doing this from memory, so it may be wrong!

I think what you're looking for, is:

=if(F10,J10,"")

I think its prob right but if you are diong it for a whole column and you want paid or p in it?

EG
£
22 Paid
33 Paid
44 Paid
55
66
77
88

99 paid
286 owed

[Gojive]

Originally Posted by drathzel

I think its prob right but if you are diong it for a whole column and you want paid or p in it?

EG
£
22 Paid
33 Paid
44 Paid
55
66
77
88

99 paid
286 owed

Oh good grief!...it's Sunday, and I'm half cut! I can't do that one just now

If you can wait till tomorrow night (when I'll be back home and have Excel available) and if no one else has fulfilled you in the meantime , then I'll try again

[drathzel]

Originally Posted by Gojive

Oh good grief!...it's Sunday, and I'm half cut! I can't do that one just now

If you can wait till tomorrow night (when I'll be back home and have Excel available) and if no one else has fulfilled you in the meantime , then I'll try again

Thank you! Have some rep in the meantime

[TheTramp]

Okie...

So, there are probably better ways to do it. But, I'd have 2 more columns (probably hidden).

So:

22 | p | =IF(B1="p", A1,0) | =IF(B1="n",A1,0)
33 | p | =IF(B2="p", A2,0) | =IF(B2="n",A2,0)
..
55 | n | =IF(B5="p", A5,0) | =IF(B5="n",A5,0)
..

Then, you can sum both rows independantly.....

Does this make sense?

I can send you an example if you want.

[ducasi]

How I'd do it, is have two columns off to the right with something like "=if(f10 = "paid",e10)" and "=if(f10 != 'paid',e10)" in each of them.

Then I'd sum those two column to get total paid and total owed.

That make sense?

Edit: Steve, stop stealing my thoughts!!!!!

[TheTramp]

Originally Posted by ducasi

How I'd do it, is have two columns off to the right with something like "=if(f10 = "paid",e10)" and "=if(f10 != 'paid',e10)" in each of them.

Then I'd sum those two column to get total paid and total owed.

That make sense?

Beat you

[TheTramp]

Originally Posted by ducasi

Edit: Steve, stop stealing my thoughts!!!!!

Sorry!

[Gojive]

I feel so inadequate now!

If I was sober and awake though, I feel sure I'd find a better way to help your sheets

[TheTramp]

Originally Posted by Gojive

I feel so inadequate now!

If I was sober and awake though, I feel sure I'd find a better way to help your sheets

Yeah. But at least you got some rep out of it.

I just re-wrote her spreadsheet for her. And all I got was kisses...

[Gadget]

Originally Posted by drathzel

I think its prob right but if you are diong it for a whole column and you want paid or p in it?

EG
£
22 Paid
33 Paid
44 Paid
55
66
77
88

99 paid
286 owed

As everyone says, but a couple more things that may help:

Select a column of cells {eg C5:C20}, then type in the formula in the first of these {eg =if(B5="P",A5,0)} and hold <ctrl> when pressing return: the formulas will copy themselves into all the selected cells as if the 'drag copy' function is used {ie C10 would contain the formula =if(B10="P",A10,0)}

If a cell uses a formula to be blank instead of containing a '0' value {eg =if(B10="P",A10,"")} and you have another formula in the row {eg F10} like =C10*1.175, then you will get an error. To resolve this, simply use =SUM(C10)*1.175 and it will ignore any text values.

To pretty things up, you could use "Conditional Formatting" and use roughly the same formula to highlight all those still to be paid {eg select rows5-20 and go to 'conditional formatting'. Insert a formula =if($B10="P",FALSE,TRUE) then select the format (eg a pink background) and hit OK.}

You can use 'names' to hold formulas or constants; you could name a cell (eg VAT) and any time the name is mentioned, the value in that cell is used (eg 1.175) so that you could have a formula of =C1*VAT and it would be the same as C1*1.175.
In a similar way, you could be on cell C5 and insert a name; call it "Paid" and in the bit that says "=Sheet1!$C$5" you simply insert the formula above "=if($B5="P",$A5,0)" {note- lock the column so that the formula can be used anywhere in the row}
Now in C5 you just say "=Paid" and the conditional formula could be =if(paid>0,FALSE,TRUE)

{If you deve deeper into this and are using formulas spanning pages and workbooks, there is a limit that requires a work-around; formulas entered in this way normally only work for the sheet they are entered on. If you need the work-around, PM me.}

[TheTramp]

So Danielle....

How much of that did you understand??

[drathzel]

Originally Posted by TheTramp

So Danielle....

How much of that did you understand??

this much

Originally Posted by gadget

To pretty things up