Help with the Formula:

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
Cell B10 = 115.99 Amount Finance
Cell B11 = 24 Term
Cell B12 = 5.30%
Cell B13 = 4.84 Payment amount

I am using this formula,
=IF(B11=C40,B10-B13*(C40-1),0)
What I am saying is that if the corresponding cell in column C has 24, (or equally to 24), then b13 (4.84) * (24-1).
the problem is that when I do this calculation using a calculator I get the desire values of $4.67, on my spreadsheet it is $4.60, and that creating a 7 cents variance.
Please advise.
Best regards,
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
whats actually in the cells , rather than rounded up
Excel has an order it does calculations

brackets first
C40-1
then it will multiply by b13
and then subtract from B10
I get 4.67 using those figures - BUT what value are actually in the cell
 
Upvote 0
It's showing 4.67

Book5
ABCDEF
1
2
3
4
5
6
7
8
9
10115.994.67
1124
125.30%
134.84
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
4024
41
Sheet4
Cell Formulas
RangeFormula
D10D10=IF(B11=C40,B10-B13*(C40-1),0)
 
Upvote 0
whats actually in the cells , rather than rounded up
Excel has an order it does calculations

brackets first
C40-1
then it will multiply by b13
and then subtract from B10
I get 4.67 using those figures - BUT what value are actually in the cell
Thanks for your reply,
The whole formula is,
=ROUND(IF($B$11>C40,$B$13,IF($B$11=C40,$B$10-$B$13*(C40-1),0)),2)
and I in D40,
when I highlight the second IF it gives me 4.602916666666667.
and as I said, when I calculate it on the calculator it gives me 4.67 and that is what I want.
 
Upvote 0
Thank you Johny Etaf and Sufyan, that means there is some problem with the data that I have, all the values, $115.99, 24, 5.3% and 2.84 are linked to another sheet and they are rounded, that must be the problem,
I really appreciate all of you.
Most regards,
Sohail.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top