Crosswords0 min ago
Excel
Anybody got an idiots guides to excel out there? trying to build a simple spreadsheet for my credit card. purchases have 2.95% added while cash paid in via paypoint has 3% deducted from it.
Tried with the 'IF' function, if I keyed in a minus figure add percentage etc. but got hopelessly lost! Any ideas, please. Wanted to keep it clear + simple, 2 columns and a running total, if you get my drift. Thx
Tried with the 'IF' function, if I keyed in a minus figure add percentage etc. but got hopelessly lost! Any ideas, please. Wanted to keep it clear + simple, 2 columns and a running total, if you get my drift. Thx
Answers
1 to 9 of 9
If you have four columns, you can have A1= Bought B1=Cost C1=Paid In D1= Running Total. In D2, enter the current figure owed. In D3, enter the following, =
09:54 Sat 20th Apr 2013
Oh @@@@!
AB doesn't like greater than and less than signs - it thinks they are HTML tags.
I'll type that again using a different sign that you will have to replace with the 'less-than' symbol.
=IF(A1^0; A1*1.0295; A1*0.97)
where A1 is the cell containing the paid-in/spent amount.
and the ^ has to be rotated 90 degrees anticlockwise to make a less-than symbol.
AB doesn't like greater than and less than signs - it thinks they are HTML tags.
I'll type that again using a different sign that you will have to replace with the 'less-than' symbol.
=IF(A1^0; A1*1.0295; A1*0.97)
where A1 is the cell containing the paid-in/spent amount.
and the ^ has to be rotated 90 degrees anticlockwise to make a less-than symbol.
If you have four columns, you can have
A1= Bought B1=Cost C1=Paid In D1= Running Total.
In D2, enter the current figure owed.
In D3, enter the following,
=IF(AND(B3="",C3=""),"",(D2+(B3*1.0295)-(C3*0.97)))
If you know what auto fill is, do that for the rest of column D. If you don’t know what that is, highlight D2, move the cursor until you see a wee ”+” in the bottom right corner and drag it down as far as you want. That will paste the formula in the rest of the column.
The “IF” and “AND” bit of the formula is so that it shows blank when there are no figures in both columns.
A1= Bought B1=Cost C1=Paid In D1= Running Total.
In D2, enter the current figure owed.
In D3, enter the following,
=IF(AND(B3="",C3=""),"",(D2+(B3*1.0295)-(C3*0.97)))
If you know what auto fill is, do that for the rest of column D. If you don’t know what that is, highlight D2, move the cursor until you see a wee ”+” in the bottom right corner and drag it down as far as you want. That will paste the formula in the rest of the column.
The “IF” and “AND” bit of the formula is so that it shows blank when there are no figures in both columns.
Ratter: thanks, great video link I learnt a lot from that,
gen: thanks, I tried, with the formula, but am too dense to get it to work!
corby loon: yours was the nearest solution I could get working, I did have to change the last minus sign for a plus to make it work. [that's not criticsm!] :)
Thanks all, now if I can get Adobe to play nicely ....!
gen: thanks, I tried, with the formula, but am too dense to get it to work!
corby loon: yours was the nearest solution I could get working, I did have to change the last minus sign for a plus to make it work. [that's not criticsm!] :)
Thanks all, now if I can get Adobe to play nicely ....!
Sorry, Ratter & others, can I adapt your formula so that the input is just one column?
a negative figure for paying out [ to which 2.95% is added] and a paying in figure [from which 3.00% is deducted]
A1= Date B1=Cost/Paid In D1= Running Total.
In D2, enter the current figure owed.
In D3, enter the following,
=IF(AND(B3="",C3=""),"",(D2+(B3*1.0295)+(C3*0.97))) . Or shall I just quit while I'm ahead!
a negative figure for paying out [ to which 2.95% is added] and a paying in figure [from which 3.00% is deducted]
A1= Date B1=Cost/Paid In D1= Running Total.
In D2, enter the current figure owed.
In D3, enter the following,
=IF(AND(B3="",C3=""),"",(D2+(B3*1.0295)+(C3*0.97))) . Or shall I just quit while I'm ahead!
1 to 9 of 9
