• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation in Formula

B

Bumblebee

New Member
ive added an explanation in the sheet, but essentially, ive got 2 figures, (think of it as the difference of figures, and that difference is either owed, or is due)
but if it is owed back, it cant be more than 85% of the initial value going back, but if it is due, then it is just the difference, but has to be as a formula, so just the answer is shown
 

Attachments

  • Declaration Calculation.xlsx
    12.3 KB · Views: 7
hi it is very good, thank you very much , the second portion of it is attached, trying to figure how to phrase it takes a bit, but im sure takes longer to do it, thank you , new file attached
 

Attachments

  • Declaration Calculation2.xlsx
    13 KB · Views: 1
hi, i think we just about there, please see attached, ive swapped e6 and E11 figures, so now if you take E11 - E6 , it should show the difference due from
where as before if E11 was less than E6, and under 10000, then nothing to show as it is now
 

Attachments

  • Declaration Calculation3.xlsx
    13.3 KB · Views: 0
ok, i think i got the rough idea of how to fix it, just dont know how to apply it, please see latest
 

Attachments

  • Declaration Calculation3.xlsx
    13.4 KB · Views: 2
sorry if this is frustrating you, been a great help, the last bit is , if the difference is too great between the 2 cells, the refund more than 15%, then it shows 0, like in the attached instance the refund should be -R3000, but we made it so it calcs not more than 15% of E6, so it should be -1800 and not 0

I think that'll be it then
 

Attachments

  • Declaration Calculation - Draft !.xlsx
    14.3 KB · Views: 2
Hi ,

There is absolutely no clarity on the precise number of rules that should be applied , and the precise definition of these rules in terms of worksheet cells ; if your rules can refer to E6 and E11 , avoiding terminology such as
the space between values becomes large
Click to expand...
, we can arrive at a solution in quick time.

What exactly should I take as the space between values becomes large ?

Narayan
 
you can, i cant, its a big jumble to me , you seem to be very good at this
Essentially, in E11 is less than E6, it means a refund is due, which would normally just be E11-E6, but that difference, that refund cannot be more than 15% of E6,
at the moment the difference is -3000, but with my above explanation, it should not be more than -1800, so when the refund difference is too big, it must go to 15% of E6
 
Hi ,

Can we specify the rule as follows :

If E11 is less than E6 , then the output will be the difference (E11 - E6) capped at a maximum (or in the negative case , minimum) value of 15% of E6.

If E11 = E6 , what would be the rule ? Or is that already taken care of in the existing formula ?

If E11 > E6 , what is the rule ? Or is that already taken care of in the existing formula ?

Narayan
 
If E11 is less than E6 , then the output will be the difference (E11 - E6) capped at a maximum (or in the negative case , minimum) value of 15% of E6.

for this point, if E11 is less than e6, and both below 10 000, then 0, but that already done

So, if E11 is less than E6, then the difference must show, which should be E11 - E6 = -3000, so negative 3000, which is a refund

But it cannot be more than 15% of E6, which is 1800, but to be accurate since it is a refund negative 15% would be more accurate? cannot be more than -1800 in this instance

it does work at the moment, as it has been showing the differences to be refunded
the only problem is that if it goes past that minimum -1800, as example to -2000, then it shows 0,
and we need it so that if it goes to that -2000, it will apply the "minimum" of -1800 instead of 0

the rest of the calculations are perfect , its just this minimum portion
 
Hi ,

See the attached file ; should it display -1,800 or 1,800 ?

Narayan
 

Attachments

  • Declaration Calculation - Draft !.xlsx
    14.3 KB · Views: 1
hi, the -1,800 is perfect, sorry for this next part though
if you go to inpit tab, and change the 60 000 000 to 70 000 000, there should still be a refund (that negative amount) but as above we just to the minimum at 15% to work, in this instance it should be E11-E6 being -1,500, only the 15% doesnt apply as it does not go over that
 
You must log in or register to reply here.
Back
Top