Necesito una fórmula

%3CLINGO-SUB%20id%3D%22lingo-sub-2849526%22%20slang%3D%22es-ES%22%3EI%20need%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2849526%22%20slang%3D%22es-ES%22%3E%3CP%3EI%20need%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20one%20column%20go%20hectares%2C%20from%201%20to%20700.%20And%20in%20another%20column%20I%20need%20a%20formula%20that%20will%20give%20me%20back.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20If%20the%20number%20of%20hectares%20is%20between%201%20to%20250%2C%20the%20formula%20has%20to%20return%20%2410000.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E-%20If%20the%20number%20of%20hectares%20is%20between%20251%20to%20400%2C%20the%20formula%20has%20to%20return%20%2420000.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E-%20If%20the%20number%20of%20hectares%20is%20between%20401%20to%20700%2C%20the%20formula%20has%20to%20return%20%2430000.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20in%20the%20column%20hectares%20I%20have%20no%20loaded%20value%2C%20or%20there%20is%20a%20zero%2C%20let%20me%20return%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20do%2C%20could%20someone%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20now%20on%20thank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2849526%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3ECommunity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2850673%22%20slang%3D%22en-US%22%3ERe%3A%20Necesito%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2850673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1186502%22%20target%3D%22_blank%22%3E%40Leandro3847%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello!%20You've%20posted%20your%20question%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Ftech-community-discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ETech%20Community%20Discussion%20space%3C%2FA%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3Ewhich%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3EExcel%20space%3C%2FA%3E%20-%20please%20post%20Excel%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2850981%22%20slang%3D%22en-US%22%3ERe%3A%20Necesito%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2850981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1186502%22%20target%3D%22_blank%22%3E%40Leandro3847%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(D3%26gt%3B%3D1%2CD3%26lt%3B%3D250)%2C%22%2410000%22%2C%3C%2FP%3E%3CP%3EIF(AND(D3%26gt%3B%3D251%2CD3%26lt%3B%3D400)%2C%22%2420000%22%2C%3C%2FP%3E%3CP%3EIF(AND(D3%26gt%3B%3D401%2CD3%26lt%3B%3D700)%2C%22%2430000%22%2C%3C%2FP%3E%3CP%3EIF(OR(D3%3D0%2CD3%3D%22%22)%2C0))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20your%20values%20in%20range%20D3%3AD80%20for%20example.%20Enter%20above%20formula%20in%20F3%20and%20copy%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUP(D3%2C%7B0.1.251.401.701%7D%2C%7B0.%22%2410000%22.%22%2420000%22.%22%2430000%22%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20you%20can%20enter%20lookup%20formula%20to%20return%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2866716%22%20slang%3D%22es-ES%22%3ERe%3A%20I%20need%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2866716%22%20slang%3D%22es-ES%22%3EDear%2C%20I%20need%20your%20help%20again.%20%3CBR%20%2F%3E%20Place%20this%20formula%26lt%3B%3D250)%3B%22%2424019%22%3BSI(Y(D45%26gt%3B%3DSI(Y(D45%26gt%3B%3D1%2CD45%3D251%2CD45%3D401%2CD45%26lt%3B%3D400)%3B%22%2431225%22%3BSI(Y(D45%26gt%3B%20%26lt%3B%3D700)%3B%22%2448800%22%3BSI(O(D45%3D0%3BD45%3D%22%22)%3B0))))%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20..%20but%20I%20have%20the%20problem%20that%20the%20value%20that%20returns%20me%20is%20like%20%22text%22%20instead%20of%20%22number%22%2C%20then%20when%20I%20want%20to%20add%20it%20to%20another%20value%2C%20I%20do%20not%20take%20it!%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Could%20you%20help%20me!!%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2867110%22%20slang%3D%22en-US%22%3ERe%3A%20Necesito%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2867110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1186502%22%20target%3D%22_blank%22%3E%40Leandro3847%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20also%20use%20the%20equivalent%20of%20LOOKUP.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LOOKUP(area%2C%7B1%2C251%2C401%3B10000%2C20000%2C30000%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20using%20Excel%20365%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20threshold%2C%20%7B1%2C251%2C401%7D%2C%0A%20%20payment%2C%20%20%20%7B10000%2C20000%2C30000%7D%2C%0A%20%20LOOKUP(area%2C%20threshold%2C%20payment))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2867118%22%20slang%3D%22en-US%22%3ERe%3A%20Necesito%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2867118%22%20slang%3D%22en-US%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20BUSCAR(area%3B%7B1%3B251%3B401%5C10000%3B20000%3B30000%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Necesito una formula.

 

En una columna van hectáreas, de 1 a 700. Y en otra columna necesito una fórmula que me devuelva..

 

- Si el número de hectáreas es entre 1 a 250, la fórmula me tiene que devolver $10000.  

- Si el número de hectáreas es entre 251 a 400, la fórmula me tiene que devolver $20000.  

- Si el número de hectáreas es entre 401 a 700, la fórmula me tiene que devolver $30000.  

 

Y si en la columna hectáreas no tengo valor cargado, o hay un cero, que me devuelva cero.

 

Como hago, alguien podría ayudarme?

 

Desde ya muchísimas gracias!!

 

6 Replies

@Leandro3847 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

@Leandro3847 

=IF(AND(D3>=1,D3<=250),"$10000",

IF(AND(D3>=251,D3<=400),"$20000",

IF(AND(D3>=401,D3<=700),"$30000",

IF(OR(D3=0,D3=""),0))))

 

Enter your values in range D3:D80 for example. Enter above formula in F3 and copy down.

 

=LOOKUP(D3,{0.1.251.401.701},{0."$10000"."$20000"."$30000"})

 

Alternatively you can enter lookup formula to return results.

Estimado, necesito su ayuda nuevamente.
Coloque esta formula =SI(Y(D45>=1;D45<=250);"$24019";SI(Y(D45>=251;D45<=400);"$31225";SI(Y(D45>=401;D45<=700);"$48800";SI(O(D45=0;D45="");0))))

..pero tengo el problema que el valor que me devuelve es como "texto" en lugar de "numero", entonces luego cuando quiero sumarlo a otro valor, no me lo toma!

Podrías ayudarme!!

@Leandro3847 

You could also use the equivalent of LOOKUP.

= LOOKUP(area,{1,251,401;10000,20000,30000})

or using Excel 365

= LET(
  threshold, {1,251,401},
  payment,   {10000,20000,30000},
  LOOKUP(area, threshold, payment))
= BUSCAR(area;{1;251;401\10000;20000;30000})