Home

What Formula do I use?

%3CLINGO-SUB%20id%3D%22lingo-sub-448959%22%20slang%3D%22en-US%22%3EWhat%20Formula%20do%20I%20use%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-448959%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20a%20car%20salesman%20with%20a%20spreadsheet%20that%20calculates%20how%20many%20units%20I%20have%20sold%20and%20my%20supergross%20(amount%20of%20money%20made%20on%20each%20vehicle%20I%20sell).%20I%20get%20paid%20based%20on%20a%20chart%20like%20this.%20What%20formula%20do%20I%20use%20to%20figure%20this%20out%3F%20I%20tried%20a%20nested%20IF(AND)%20but%20there%20has%20to%20be%20an%20easier%20way.%20Just%20to%20be%20clear%20on%20the%20chart%2C%20If%20I%20sell%201-8%20cars%20and%20my%20supergross%20is%20%242%2C299%20or%20less%20I%20make%20%2450%2Fcar.%20How%20can%20I%20calculate%20the%20amount%20I%20make%20on%20each%20car%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-448959%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449192%22%20slang%3D%22en-US%22%3ERe%3A%20What%20Formula%20do%20I%20use%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449192%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formula%20in%20G3%20of%20the%20attached%20file%20is%3A%3C%2FP%3E%3CP%3E%3DINDEX(B2%3AD6%2C%3C%2FP%3E%3CP%3EMATCH(G1%2CA2%3AA6%2C1)%2C%3C%2FP%3E%3CP%3EMATCH(G2%2CB1%3AD1%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449193%22%20slang%3D%22en-US%22%3ERe%3A%20What%20Formula%20do%20I%20use%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449193%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formula%20in%20G3%20of%20the%20attached%20file%20is%3A%3C%2FP%3E%3CP%3E%3DINDEX(B2%3AD6%2C%3C%2FP%3E%3CP%3EMATCH(G1%2CA2%3AA6%2C1)%2C%3C%2FP%3E%3CP%3EMATCH(G2%2CB1%3AD1%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449194%22%20slang%3D%22en-US%22%3ERe%3A%20What%20Formula%20do%20I%20use%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449194%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formula%20in%20G3%20of%20the%20attached%20file%20is%3A%3C%2FP%3E%3CP%3E%3DINDEX(B2%3AD6%2C%3C%2FP%3E%3CP%3EMATCH(G1%2CA2%3AA6%2C1)%2C%3C%2FP%3E%3CP%3EMATCH(G2%2CB1%3AD1%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E
l3p3rkan
Occasional Visitor

I am a car salesman with a spreadsheet that calculates how many units I have sold and my supergross (amount of money made on each vehicle I sell). I get paid based on a chart like this. What formula do I use to figure this out? I tried a nested IF(AND) but there has to be an easier way. Just to be clear on the chart, If I sell 1-8 cars and my supergross is $2,299 or less I make $50/car. How can I calculate the amount I make on each car?

 

1 Reply

The formula in G3 of the attached file is:

=INDEX(B2:D6,

MATCH(G1,A2:A6,1),

MATCH(G2,B1:D1,1))

Related Conversations