Using IF to dismiss negative numbers (1 Viewer)

[CryLittleSister]

Hi

I have the following expression I use to round down when breaking down Fee Proposals:

Int([PriceQuoted]/[HourlyRate]*2-0.5)/2

This works perfectly, except if I do not enter a price I get a figure of -0.5, obv. Could I use the If function to have it give me zero if I leave the price blank?

 

[CryLittleSister]

Actually, there's a problem with the calculation I have. What I'm trying to do is divide the price by the hourly rate and then round down to the nearest half an hour. Unfortunately the expression I'm using is rounding down when i don't want it to, so if I quote £700 at an hourly rate of £70, it's giving me 9.5 instead of 10. Can I fix this or is it getting too complicated?

 

[NauticalGent]

Give this a read:

http://allenbrowne.com/round.html

 

[CryLittleSister]

Brilliant, thank you! I used Fix to fix the first problem. Do you know how I could fix the issue of it rounding down when I don't want it to (when the price is already divisible by the hourly rate)?

 

[NauticalGent]

Do you know how I could fix the issue of it rounding down when I don't want it to (when the price is already divisible by the hourly rate)?

If this means that when the division is done you are left with a whole number (nothing to the right of the decimal), then yes that IS possible. To the best of my knowledge though I would have to do it with VBA. Are you familiar with the VBE?

There MAY be a way to do this without VBA but I would have to consult Dr. Google.

 

[CryLittleSister]

Not very, I'm still pretty new to all this.

See, it's slightly more complicated than that.
I notice that £710/70 would still give me 9.5 instead of 10 due to how I've formed the first calculation. I'm not sure if there's a simpler way to have done that in the first place rather than trying to do so as an afterthought

 

[CryLittleSister]

I'm thinking something along the lines of (I know isn't how to express so I'm hoping someone can understand me and translate):

PQ=Price Quoted
HR=Hourly Rate

If [PQ] is divisible by

---

then [PQ]/

---

.
If [PQ] is not divisible by

---

then Fix([PriceQuoted]/[HourlyRate]*2-0.5)/2

But then I'm still left with the problem that £710/70=9.5..

 

[isladogs]

When you say not divisible what do you actually mean?

I ask because using your example 710 is of course divisible by 70 but there is a remainder.

Suggest you give several examples together with the desired result so someone can give you a formula. It shouldn't be difficult

 

[CryLittleSister]

I DID IT
nearly..

I've figured out how to use int to make 700/70=10. But I still have the problem of 710/70=9.5.

I currently have this:

IIf([PriceQuoted]/[HourlyRate]=Int[PriceQuoted]/[HourlyRate],[PriceQuoted]/[HourlyRate],Fix([PriceQuoted]/[HourlyRate]*2-0.5)/2)


any ideas?

 

[CryLittleSister]

When you say not divisible what do you actually mean?

I ask because using your example 710 is of course divisible by 70 but there is a remainder.

Suggest you give several examples together with the desired result so someone can give you a formula. It shouldn't be difficult

The expression I'm using works out the number of hours quoted, rounded down to the nearest half hour (see above post). But the way I get this to work is by doubling, removing 0.5 and then halving. This works most of the time but if the remainder left when dividing is too small, it's dropping down by an extra half.

I've fixed the problem where 700/10=9.5 with an Iif expression but i'm unsure of how to do so for small remainders

 

[CryLittleSister]

I took away the -0.5 and it works but now I don't understand why because I'd come up with the original formula a while go...

But it works thanks for your help guys

 

[NauticalGent]

It's always better when the OP stays in the fight and answers his/her own question. Well done...

 

[CryLittleSister]

This is really fun to work out.

Turns out I didn't need the If at all, just to get rid of the -0.5 and to change Int to Fix. Now it's far simpler and works perfectly

 

[isladogs]

As I said before:

It shouldn't be difficult