Help with numbers

Question

Level 1

8 points

Help with numbers

I have an issue which I would appreciate help.

I have a spreadhseet of which a simplified extract is included.

I have used formula in column D =IF(B2>20,(0.45×B2),"£10")

What I am trying to achieve, but just keep getting lost is Column D is per formula, however I would like it to be able to take into account column C. If Column C =1h, then Column D should be =IF(B2>20,(0.45×B2),"£10") but add an extra £10 to the final value. If Column C is blank than formula in D to work as it is at moment. And if Column C is >1h, then the final result in D to increase in increment of 10, so 1h=10, 2h=20, 3h=30 etc etc.

I thought I could do it with and IF&AND Formula but I keep getting syntax errors, or just getting no result.

This is a simplified version of a larger more complex table but using this as proof of concept.

Any help would be appreciated.

Posted on Sep 28, 2022 4:14 AM

Reply

Answer 1

Top-ranking reply

Lovecross

Level 2

185 points

Sep 28, 2022 7:37 AM in response to flatfoot471

Based on the assumption that column C is formatted as Duration (or Automatic), ie. not text, and column D is formatted as Currency, if I understand what you are after correctly, the following should work.

IF(B2>20,(0.45×B2),IF(C2="",10,10×DUR2HOURS(C2)))

Note that I have changed cell C5 to 2h to demonstrate the incremental increase.

Reply

Answer 2

SGIII

Level 8

36,434 points

Sep 28, 2022 7:47 AM in response to flatfoot471

I not 100% certain I have followed all your logic, but I think you can try something like this:

In D2, filled down:

=MAX(10,B2*0.45)+10*IFERROR(DUR2HOURS(C2),0)

Note that you are better leaving the £ out of the formula itself (as that will give you a string instead of a number. Instead format the column as currency.

SG

Reply