If cell value above xyz equal to then use...

[like numbers]

Hi,

First time poster, so apologies to anyone if I am asking a question that has been posted and answered many times before...

I was offered a few new contracts and was trying to work the out break evens with the mileage driving (excuse the pun) my decision.

I tried to find an excel formula for the mileage by trial and error by myself, did a fair number of google searches with further excel experiments and even tried hiring a book from the local library; admittedly the library book was after manually punching in the figures myself whilst doing my sums, but now I'm curious if there is a clever way of calculating the below; at present I would say I'm glad of all this as I now know all about conditional formatting and want to learn a lot more about excel

I needed the formula to recognise whether the sum of one cell multiplied by another cell was/is above or below 10000, so if below 10000 to multiply the sum of the 2 first cells by 0.45 BUT if above 10000 to use the following calculation (4500+((sum of 2 cells minus 10000)*.2)))

Thanks in advance

[AliGW]

=if((a1*b1)<1000,(a1*b1)*0.45,(4500+((a1+b1)-1000)*0.2))

This will force the second condition if the multiplication is greater than or equal to 1000.

[like numbers]

many thanks, that was mighty quick

I will give that formula a go

[AliGW]

many thanks, that was mighty quick

I will give that formula a go

Lots of practice!!!

Hope it works ...

[like numbers]

close but not quite...

I tweaked it to the below just to reflect that at 10000 miles is the actual cut off (my error above) and then the second calc adjustment, but with this in context I think I know understand if statements

=IF((A1*B1)<10001,(A1*B1)*0.45,(4500+((A1*B1)-10001)*0.25))

many thanks again

[AliGW]

Excellent!