formula If... Then

%3CLINGO-SUB%20id%3D%22lingo-sub-2394351%22%20slang%3D%22en-US%22%3Eformula%20If...%20Then%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2394351%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20trying%20to%20write%20a%20formula%20for%20cell%20B1%3C%2FP%3E%3CP%3Eif%20the%20value%20in%20cell%20A1%20is%20between%200%20and%20%2410%20then%20B1%20%3D%20%241%3C%2FP%3E%3CP%3Eif%20the%20value%20in%20cell%20A1%20is%20greater%20than%20%2410%20and%20less%20than%20or%20equal%20to%20%2425%20then%20B1%20%3D%20%242%3C%2FP%3E%3CP%3Eif%20the%20value%20in%20A1%20is%20greater%20than%20%2425%20and%20less%20than%20or%20equal%20to%20%2440%20then%20B1%20%3D%20%243%3C%2FP%3E%3CP%3Eetc%3C%2FP%3E%3CP%3EThe%20last%20one%20will%20be%20if%20the%20value%20in%20A1%20is%20greater%20than%20%2450%20then%20B1%20%3D%20%245%3C%2FP%3E%3CP%3EMy%20preference%20is%20that%20the%20%24%20values%201%2C%202%2C%203%2C%204%2C%205%20and%2010%2C%2025%2C%2040%2C%2050%20etc%20are%20hard%20entered%20into%20data%20cells%20to%20allow%20simple%20change%20of%20the%20parameters%20and%20values%20at%20a%20later%20date%3C%2FP%3E%3CP%3ECan%20anyone%20help.%3C%2FP%3E%3CP%3ETa%20Mike%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2394351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2394374%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20If...%20Then%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2394374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065394%22%20target%3D%22_blank%22%3E%40MikeH2021%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-28%20at%2006.08.26.png%22%20style%3D%22width%3A%20505px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284541iC0B0C6A9C5252258%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-28%20at%2006.08.26.png%22%20alt%3D%22Screenshot%202021-05-28%20at%2006.08.26.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EA%20working%20example%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi - I am trying to write a formula for cell B1

if the value in cell A1 is between 0 and $10 then B1 = $1

if the value in cell A1 is greater than $10 and less than or equal to $25 then B1 = $2

if the value in A1 is greater than $25 and less than or equal to $40 then B1 = $3

etc

The last one will be if the value in A1 is greater than $50 then B1 = $5

My preference is that the $ values 1, 2, 3, 4, 5 and 10, 25, 40, 50 etc are hard entered into data cells to allow simple change of the parameters and values at a later date

Can anyone help.

Ta Mike

 

1 Reply

@MikeH2021 Try this:

Screenshot 2021-05-28 at 06.08.26.png

I've used named ranges to make the formula easier to read and maintain.

A working example in the attached file.