PDA

View Full Version : Microsoft excel equations



drathzel
9th-April-2006, 03:47 PM
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?:hug:

Gojive
9th-April-2006, 03:59 PM
Danielle,

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

I think what you're looking for, is:

=if(F10,J10,"")

:)

drathzel
9th-April-2006, 04:05 PM
Danielle,

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

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
9th-April-2006, 04:14 PM
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 :eek: :wink:

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 :waycool: , then I'll try again :flower:

drathzel
9th-April-2006, 04:16 PM
Oh good grief!...it's Sunday, and I'm half cut! I can't do that one just now :eek: :wink:

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 :waycool: , then I'll try again :flower:

Thank you! Have some rep in the meantime:hug:

TheTramp
9th-April-2006, 04:19 PM
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? :flower:

I can send you an example if you want.

ducasi
9th-April-2006, 04:23 PM
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!!!!! :eek:

TheTramp
9th-April-2006, 04:24 PM
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 :D

TheTramp
9th-April-2006, 04:25 PM
Edit: Steve, stop stealing my thoughts!!!!! :eek:

Sorry!

Gojive
9th-April-2006, 04:29 PM
I feel so inadequate now! :tears:

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

TheTramp
9th-April-2006, 04:47 PM
I feel so inadequate now! :tears:

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

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... :rolleyes:

Gadget
9th-April-2006, 09:59 PM
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
9th-April-2006, 10:12 PM
So Danielle....

How much of that did you understand?? :D

drathzel
9th-April-2006, 10:15 PM
So Danielle....

How much of that did you understand?? :D

this much


To pretty things up

:flower: